Converting macro variable value to date format

[alyssaxm]

I have a macro variable that stores the filename.

%let file = ABC_DE___January_7__2019_2019_12_20;

I extracted the date January 7, 2019 and stored it into macro variables.

%let file_day = %sysfunc(scan(&file., 4, _)); --> outputs 7
%let file_mth = %sysfunc(scan(&file., 3, _)); --> outputs January
%let file_yr = %sysfunc(scan(&file., 5, _));  --> outputs 2019

Then I want to concatenate the variables above to create the file date. It outputs 2019-January-7.

%let file_date = %sysfunc(catx(-, &file_yr., &file_mth., &file_day.));

In the DATA step, I want to be able to use this file_date macro variable but in yymmdd10. format. However, I cannot convert it to this format even if I used the INPUT function. Error log shows that "January is uninitialized"

data test;
set prevdata;

	format s_date yymmdd10.;
	s_date = input("&file_date.", yymmdd10.);

run;

Can you help shed some light as to why this is the case? I am a beginner when it comes to SAS and I probably am missing out on something. Thanks.

[novinosrin · Accepted solution]

Hi @alyssaxm  Keep it simple,

%let file_day = %sysfunc(scan(&file., 4, _));
%let file_mth =%substr(%scan(&file,3, _),1,3); 
%let file_yr = %sysfunc(scan(&file., 5, _)); 


%let file_date =  &file_day&file_mth&file_yr;



data test;
set prevdata;
	format s_date yymmdd10.;
	s_date = "&file_date"d;

run;

And actually you do not need %sysfunc to invoke Datastep/proc sql functions as you can use %scan instead of scan. So to make it cleaner, you could follow the below style

%let file_day = %scan(&file., 4, _));
%let file_mth =%substr(%scan(&file,3, _),1,3); 
%let file_yr = %scan(&file., 5, _); 


%let file_date =  &file_day&file_mth&file_yr;



data test;
set prevdata;
format s_date yymmdd10.;
s_date = "&file_date"d;
run;

[alyssaxm]

Amazing! I didn't know that I can actually remove %sysfunc and use %scan instead. Now, the code looks way simpler! I learned something today. 🙂 Thanks a lot!

[Jagadishkatam]

Please try the anydtdte20. format

data test;
set prevdata;
	s_date = input("&file_date.", anydtdte20.);
	format s_date yymmdd10.;
run;

[Astounding]

First, let's clean up the code a bit.  All the %SYSFUNCs can go.  If you switch from SCAN to %SCAN, you can use:

%let file_day = %scan(&file., 4, _); --> outputs 7
%let file_mth = %scan(&file., 3, _); --> outputs January
%let file_yr = %scan(&file., 5, _);  --> outputs 2019

Macro language can easily combine strings.  So in similar fashion:

%let file_date = &file_yr.-&file_mth.-&file_day.;

Finally, to use this date in a DATA step, it would be most convenient to use  7Jan2019  as the date format.  So:

%let file_date2 = &file_day.%substr(&file_mth., 1, 3)&file_yr;

Within the DATA step no functions are needed.  You can refer to a SAS date using this syntax:

"&file_date2."d