How do i convert a date column using SAS Macro?

0
votes

I am a SAS Developer. I currently have a script that will read from a table column that is in datetime format. In this script, it looks something like this:

data a; batch_dttm = '01Jan2011:00:00:00'dt; run;

proc sql; select batch_dttm format=16.0 into:batch_dttm from a; quit;

So when I assign it to macro variable, it is actually assigning the value of 2930485 into batch_dttm.

The problem is, when I want to resolve this &batch_dttm in another job at a later stage, i have to use this:

input(&batch_dttm,16.0)

to convert 2930485 into date.

I don't want to resolve in this way as this is the only Macro variable that has to be resolved with this input function. I want to assign 01Jan2011:00:00:00 (as text?) in PROC SQL INTO statement so that i dont have to use input conversion anymore.

I want to call &batch_dttm as datetime format in another script. I only want to resolve the datetime using "&BATCH_DTTM"dt instead of input(&batch_dttm,16.0). I believe there is a step to convert 01Jan2011:00:00:00 into text without changing it to 2930485. Is there anyway to do so?

How can I add 1 more step to make me resolve the macro in below script:

"&batch_dttm"dt

2
sas

2 Answers

3
votes

Why do you think you need to add the INPUT() function? You can just use the value of the macro variable to generate the number of seconds, 2930485 in your example, into your code.

SAS stores datetime values as a number of seconds, so these two expressions are the same:

where batch_dttm = 2930485 ;
where batch_dttm = '01JAN2011:00:00:00'dt ;

Which means you can just use code like this to use your original macro variable.

where batch_dttm = &batch_dttm ;

If you do need to have the human friendly text in the macro variable, perhaps to use in a title statement, then just change the format you use when creating the macro variable.

select batch_dttm format=datetime20. into :batch_dttm trimmed ...
...
title "Data as of &batch_dttm";
where batch_dttm = "&batch_dttm"dt ;

You can also use %sysfunc() to call PUTN() to change the existing number of seconds into that style if you want.

select batch_dttm format=32. into :batch_dttm trimmed ...
...
title "Data as of %sysfunc(putn(&batch_dttm,datetime20.))";
where batch_dttm = &batch_dttm ;
0
votes

Like this?

proc sql; 
    select put(batch_dttm, datetime20.) into:batch_dttm from a; 
quit;

%put &batch_dttm;