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.
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;
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;
Please try the anydtdte20. format
data test;
set prevdata;
s_date = input("&file_date.", anydtdte20.);
format s_date yymmdd10.;
run;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.