topic Re: SAS date format conversion and assigning to macro variables in SAS Programming

SAS date format conversion and assigning to macro variables

Emoji — Tue, 05 Nov 2024 19:57:42 GMT

Hello all,

I have the following code and trying to assign the date to _hist macro and I need to make sure that that value assigned to _hist is going to be DD-MON-YYYY (like 05-Nov-2024). However, my code assign value of 23685 to the macro which is not what I want (date in the input table is in format/informat of DATETIME20.)

DATA WORK.RECENT;

SET WORK.INPUT END = ISLAST;

DATE = DATEPART(DATE);

IF ISLAST THEN

OUTPUT;

FORMAT OPR_DATE DATE11.;

RUN;

DATA _NULL_;
SET WORK.RECENT;
CALL SYMPUT('_HIST',DATE);
RUN;

%PUT &=_HIST;

Re: SAS date format conversion and assigning to macro variables

Stu_SAS — Tue, 05 Nov 2024 20:25:02 GMT

Hey @Emoji! The reason this is occurring is because formats are for visually displaying a value. The underlying value you have is still just a number - in this case, it's the number of days since Jan 1st 1960. One easy way to remember this: formats are for you to view.

To make sure your macro variable takes on the date format you want, convert the SAS date to a string of the format you want with a PUT function. For example:

data _null_;
    call symputx('_hist', put('05NOV2024'd, ddmmyyd10.));
run;

%put &=_HIST;

Log:

_HIST=05-11-2024

Bonus

Now if you want to optimize your code, you can actually use SQL instead and do it all in one step:

proc sql noprint;
    select max(opr_date) format=ddmmyyd10.
    into :_hist
    from input;
quit;

Okay, now you might be wondering why you don't need to use PUT here. SQL SELECT INTO is an exception to the rule. It will send your formatted value to the macro variable automatically.

Re: SAS date format conversion and assigning to macro variables

Tom — Tue, 05 Nov 2024 21:40:39 GMT

Another tip. Only using the ancient CALL SYMPUT() routine if it is important that the resulting macro variable contains leading and/or trailing spaces. Otherwise use the new (available since at least version 6 of SAS) CALL SYMPUTX() routine, which automatically remove the trailing and leading spaces.