SAS current date/timestamp

user_18395890 0
Viewed 402

I am trying to get insert the timestamp, current date and current time into a table using macro, but values are not getting displayed as expected. Can someone help on this please?

Also i m trying to write the SQL return code and message, but it displayed nothing.

%MACRO INS;
 data _NULL_;

   call symput('currdatets',datetime());
   call symput('currdate',today());
   call symput('currtime',timepart(datetime()));


 %put  currdatets>  &currdatets;
 %put  currdater--2> &currdate;
 %put  currtime---2> &currtime;

run;

proc sql;

    CONNECT TO DB2 
  insert into table
    (entrytime, rundate, runtime)
  values 
    (&currdatets,&currdate,&currtime)

    DISCONNECT FROM DB2;
    QUIT;
    %PUT &SQLXMSG;
    %PUT &SQLXRC ;
%MEND;


WARNING: Apparent symbolic reference CURRDATETS not resolved.
currdatets>   &currdatets
WARNING: Apparent symbolic reference CURRDATE not resolved.
currdater--2>  &currdate
WARNING: Apparent symbolic reference CURRTIME not resolved.
currtime---2>  &currtime

WARNING: Apparent symbolic reference SQLXMSG not resolved.
&SQLXMSG
WARNING: Apparent symbolic reference SQLXRC not resolved.
&SQLXRC
sas-macro sas
1 Answers

Your first three macro-variables are not resolved because you specified the %put statements before the end of the data _null_ step (i.e., before the run;). symput assigns values produced in a DATA step to macro variables during program execution.

Use symputx instead of symput. It does not change the result though, but symput gives you a message on the log about the conversion, while symputx does not. Moreover, symputx takes the additional step of removing any leading blanks that were caused by the conversion.

As for the two SQL Pass-Through automatic macro-variables you will need to provide us with more information. I don't know if intended or not, but you seem to use an explicit Pass-Through connection. If so, you might be missing information to connect to the server (e.g., connect to db2 (dsn= "xxxx")).
The automatic macro-variables SQLXRC and SQLXMSG are reset after each SQL Procedure Pass-Through Facility statement has been executed. If they are not resolved it means there were not any.

By the way, according to the documentation, you may want to use %SUPERQ() with SQLXMSG

SQLXMSG contains descriptive information and the DBMS-specific return code for the error that is returned by the pass-through facility.

Note: Because the value of the SQLXMSG macro variable can contain special characters (such as &, %, /, *, and ;), use the %SUPERQ macro function when printing the following value: %put %superq(sqlxmsg);

%macro ins();
data _null_;
    call symputx('currdatets',datetime());
    call symputx('currdate',today());
    call symputx('currtime',timepart(datetime()));
run;

%put  currdatets> &currdatets. | currdate> &currdate. | currtime> &currtime.;

proc sql;
connect to db2;
insert into table (entrytime, rundate, runtime)
values (&currdatets,&currdate,&currtime);
disconnect from db2
;
quit;
    
%put %superq(sqlxmsg);
%put &sqlxrc. ;
%mend;

%ins();

currdatets> 1966238593.2 | currdate> 22757 | currtime> 33793.19107
user_104686130
Related