topic Re: Convert sybase string column into SAS Date in SAS Data Management

Convert sybase string column into SAS Date

etl_tool — Fri, 24 Apr 2015 02:27:23 GMT

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.

How to convert the datetime character string to SAS datetime value? (ANYDTDTM and MDYAMPM formats) | StudySAS Blog

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

Re: Convert sybase string column into SAS Date

ballardw — Fri, 24 Apr 2015 15:43:24 GMT

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.

Re: Convert sybase string column into SAS Date

etl_tool — Fri, 24 Apr 2015 16:09:10 GMT

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.

Re: Convert sybase string column into SAS Date

etl_tool — Fri, 24 Apr 2015 18:03:36 GMT

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.

Re: Convert sybase string column into SAS Date

ballardw — Fri, 24 Apr 2015 19:08:38 GMT

Show the submitted code with the error messages.

Re: Convert sybase string column into SAS Date

etl_tool — Sun, 26 Apr 2015 02:20:45 GMT

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