Insert DATETIMESTAMP values into Oracle table using SAS Macro Variables - Stack Overflow

admin管理员组

文章数量:1391969

I have a SAS dataset which holds todays date and datetime. My requirement here is to create SAS macro variables which hold the DATE and DATETIME values. And then Update those values in Oracle table. Could you please help me what formats I need to use to update date/timestamp values into Oracle table.

data customer;
 input cust_id cust_login_dt cust_login_ts;
 cust_id=100;
 cust_login_dt=today(); /* Storing date values in SAS Format /
 cust_login_ts=datetime(); / Storing datetime values in SAS Format */
run;

proc sql;
 select cust_id,cust_login_dt ,cust_login_ts i
 Into :custid,:custlgndt,:custlgnts
 from customer; 
quit;

/* Now I need to update above values in Oracle table using SQL Passthrough */

proc sql;
connect to oracle(user=xxx password=xxxx path=custora1);
execute (
 upate cust_fact 
 set lgn_dt=&custlgndt,lgn_ts=&cuslgnts
 where cust_id=&custid.
) by oracle;
execute(commit); 
quit;

If I execute above query, Update query is failing with Invalid values to the input columns error.

Could you please help me to update SAS date and SAS Datetime values into Oracle table using SAS macro variables.

I have a SAS dataset which holds todays date and datetime. My requirement here is to create SAS macro variables which hold the DATE and DATETIME values. And then Update those values in Oracle table. Could you please help me what formats I need to use to update date/timestamp values into Oracle table.

data customer;
 input cust_id cust_login_dt cust_login_ts;
 cust_id=100;
 cust_login_dt=today(); /* Storing date values in SAS Format /
 cust_login_ts=datetime(); / Storing datetime values in SAS Format */
run;

proc sql;
 select cust_id,cust_login_dt ,cust_login_ts i
 Into :custid,:custlgndt,:custlgnts
 from customer; 
quit;

/* Now I need to update above values in Oracle table using SQL Passthrough */

proc sql;
connect to oracle(user=xxx password=xxxx path=custora1);
execute (
 upate cust_fact 
 set lgn_dt=&custlgndt,lgn_ts=&cuslgnts
 where cust_id=&custid.
) by oracle;
execute(commit); 
quit;

If I execute above query, Update query is failing with Invalid values to the input columns error.

Could you please help me to update SAS date and SAS Datetime values into Oracle table using SAS macro variables.

• oracle-database
• sas
• sas-macro

Share Improve this question Follow edited Mar 15 at 20:25 Tom 51.8k22 gold badges1818 silver badges3434 bronze badges asked Mar 15 at 19:29 Sudhakar NelapatiSudhakar Nelapati 333 bronze badges 5

• How would you write that Oracle UPDATE statement if you were using some other tool to submit it to Oracle? Once you know what syntax you need in Oracle then place that syntax into the macro variables. – Tom Commented Mar 15 at 20:26
• Actually it also requires that you know how your client environment (SAS macro?) stores its variables. Is &custlgndt a string, a number, or some proprietary date datatype? If a string, what format is it in (can you show a sample value)? What exactly is being will determine what you need to do to update a date column in Oracle. – Paul W Commented Mar 16 at 0:14
• The SAS macro processor is a TEXT pre-processor. So all macro variables are just strings. – Tom Commented Mar 16 at 3:35
• See this similar question: stackoverflow/questions/74626017/… – Tom Commented Mar 16 at 3:35
• I didn't give the background of my scenario. I am doing inserts with proc append and updates with proc sql Update. However I don't see any issue with proc append, have issue with updates. Updates are failing with Insert/update serialization error at table level. So planning to use SQL Pass through using Execute statements with external commit. To do this, need help to update date and time stamp values from SAS dataset to Oracle table. – Sudhakar Nelapati Commented Mar 16 at 18:56

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

When the macro pre-processor replaces the macro variable references with the text strings they contain the result needs to be valid Oracle code. So your goal should be to create something like:

 upate cust_fact 
 set lgn_dt=DATE '2025-03-15'
   , lgn_ts=TIMESTAMP '205-03-15 09:10:15'
 where cust_id=100

Which means you want the macro variables to contain text that looks like this:

%let custlgndt=DATE '2025-03-15';
%let cuslgnts=TIMESTAMP '2025-03-15 09:10:15';
%let cust_id=100;

Which you can achieve by running a query like this:

 select cust_id
      , 'DATE ' || quote(put(cust_login_dt,yymmdd10.),"'") 
      , 'TIMESTAMP '|| quote(translate(put(cust_login_ts,e8601dt19.),' ','T'),"'")
 into :custid trimmed
    , :custlgndt
    , :custlgnts
 from customer; 

本文标签： Insert DATETIMESTAMP values into Oracle table using SAS Macro VariablesStack Overflow