SAS date format conversion and assigning to macro variables

[Emoji]

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;

[Stu_SAS · Accepted solution]

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. 

[Tom]

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.