Insert DATE/TIMESTAMP values into Oracle table using SAS Macro Variables

Question

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.

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
– 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
– 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
– Tom, Commented Mar 16 at 3:35
See this similar question: stackoverflow.com/questions/74626017/… — Tom
– 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
– Sudhakar Nelapati, Commented Mar 16 at 18:56

Tom · Accepted Answer · 2025-03-17 13:28:37Z

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;

edited Mar 17 at 13:28

answered Mar 16 at 3:32

Tom

52.3k2 gold badges19 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sudhakar Nelapati Mar 16 at 18:48

Thank you so much for your response. I will do the testing by using your suggestion and will let you know.

Collectives™ on Stack Overflow

Insert DATE/TIMESTAMP values into Oracle table using SAS Macro Variables

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related