Convert Macro variable datetime into date9

[sarahzhou]

Hi,

I have a macro variable from proc sql select into:DATA_LOAD_DT, which is "20OCT2022:19:41:27.933" in the result.

SYMBOLGEN: Macro variable DATA_LOAD_DT resolves to 20OCT2022:19:41:27.933

How can I convert this Marcro variable DATA_LOAD_DT into "20OCT2022"?

I have tried:

%let dl_date=%sysfunc(put(datepart("&DATA_LOAD_DT."D), date.9.);
%put dl_date;

error: The PUT function referenced in the %SYSFUNC or %QSYSFUNC macro function is not found.

Please advise.

Thank you!

[ballardw]

Macro variables are text. So the date time or datetime related functions that expect a numeric value will not work with formatted values like 20OCT2022:19:41:27.93.

Here is a sequence of steps that accomplish what you want with macro functions:

%let dt=20OCT2022:19:41:27.93;

%let dtnum = %sysfunc(inputn(&dt.,datetime.));
%put dtnum is: &dtnum.;
%let date=%sysfunc(datepart(&dtnum.)) ;
%put date is: &date. ;

%let datefmt=%sysfunc(putn(&date,date9.));
%put datefmt is:&datefmt.;

Note: Sysfunc requires PutN and InputN or PutC and InputC for numeric and character arguments so that the macro processor know which you want as all macro values are character.

Also, formatted values for dates, time and date times should only be used in the places where people need to see the text. For comparisons, calculations and any function using the value it is much better to create the numeric representation of the date, time or datetime value as the formatted values will require additional work.

I did not pay attention to the decimal portion of the seconds in the datetime value. If you need the decimal portion it is up to you to verify proper use. Depending on how you make that original macro variable you should consider that aspect before including decimals.

[sarahzhou]

@ballardw , thank you! It works! 

I will practice more.