I need your help.
I'm trying to convert a string column which is in sybase in the below format into SAS date.
The sybase table has string values like this
2015-04-23 04:04:46.517
2015-04-22 04:04:35.162
2015-04-21 04:04:43.646
I need to get the max of these values and store it in a max_tmsp variable and get the records where last_updt_tmsp > max_tmsp. I wrote a precode before the job starts but it is not working.
I referred to the below link and tried to write some code.
All this code is in Precode before the job starts.
proc sql noprint;
SELECT
select max(input(PROPERTY_VAL, MDYAMPMw.d)) into :last_updt_tmsp
from sybase_lib.prop_vals where property_key='last.update.date';
quit;
format &last_updt_tmsp. DATETIME18.;
data _null_;
call symput('lst_cre_dttm',"'"||"&last_updt_tmsp."||"'dt");
run;
%put lst_cre_dttm=&lst_cre_dttm
You really should be getting errors and post the errors.
The following line most likely should be in a dataset. But macro variables do not have formats, they are all string.
format &last_updt_tmsp. DATETIME18.;
If the data you are trying to convert is yyyy-mm-dd hh:mm:ss.ff then you do not want to use MDYAMPMw.d as that reads data as mmddyyyy hh:mm:ss AM/PM.
You want the informat ymddttm25. The value of max however is going to be a number such as 1521285787.40000.
If you want the macro variable to look like 21APR2015:04:04:44 then you need something like
select put((max(input(PROPERTY_VAL, MDYAMPMw.d))),datetime25.) into :last_updt_tmsp
However since it looks like you want to make a datetime literal with the data _null_ step you should:
Not worry about the "format" of the macro variable if you are planning in using it in code later.
Thanks Ballardw. I'll try, however I am having difficulty where the job sometimes does not read the precode and the variable is not getting read.
Getting this error in the log
ERROR 22-322: Syntax error, expecting one of the following: a format name, ?.
ERROR 76-322: Syntax error, statement will be ignored.
Show the submitted code with the error messages.
Thanks I got this figured out with the help from stachoverflow.
Here's the solution
proc sql noprint;
SELECT
select max(input(PROPERTY_VAL, anydtdtm24.)) format=datetime22.3 into :last_updt_tmsp from sybase_lib.prop_vals where property_key='last.update.date';
quit;
format &last_updt_tmsp. DATETIME18.;
data null;
call symput('lst_cre_dttm',"'"||"&last_updt_tmsp."||"'dt");
run;
%put lst_cre_dttm=&lst_cre_dttm
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.