Hi,
I have file names with "Sample Response August 2 2016.xlsx", please let me know how to extract the month year date from file name and update in a variable instead of a hardcoded value like mentioned in below sas program.i.e. Received_dt variable
filename fnames pipe 'ls /user/path/*.xlsx"';
data fnames;
infile fnames pad missover;
input @1 filename $255.;
n=_n_;
run;
proc sql noprint; select count(filename) into :num from fnames; quit;
%macro file_process;
%do i=1 %to #
proc sql noprint;
select strip(filename) into :filename from fnames where n=&i;
quit;
%let file_proc = &filename;
libname XLSFILE XLSX "&file_proc";
options validvarname=v7;
options SYMBOLGEN MPRINT;
PROC SQL;
create table work.data_raw as
(select * from XLSFILE.SHEET1);
quit;
data work.data(rename=(number=number_1));
set work.data_raw;
format Received_dt mmddyy10.;
Received_dt = "02AUG2016"d;
run;
PROC SQL;
create table work.data_raw_2 as
(select * from xlsFile.'SHEET2$A3:L2000'n);
quit;
data work.data2;
set work.data_raw_2;
format Load_dt mmddyy10.;
format Received_dt mmddyy10.;
Received_dt = "02AUG2016"d;
run;
%end;
%mend;
%file_process;;
The easiest place to do this would be in your initial DATA step:
input @1 filename $255.;
length mon $ 3 year $ 4 day $ 2;
year = scan(filename, -1);
day = scan(filename, -2);
mon = scan(filename, -3);
At this point, all the pieces are character, so it is possible you may need to convert some of them later ... depending on what you are trying to achieve with them.
Note that the LENGTH statement defines MON as $ 3, so you already have the three-character abbreviation there (suitable for a date literal such as 02Aug2016).
The easiest place to do this would be in your initial DATA step:
input @1 filename $255.;
length mon $ 3 year $ 4 day $ 2;
year = scan(filename, -1);
day = scan(filename, -2);
mon = scan(filename, -3);
At this point, all the pieces are character, so it is possible you may need to convert some of them later ... depending on what you are trying to achieve with them.
Note that the LENGTH statement defines MON as $ 3, so you already have the three-character abbreviation there (suitable for a date literal such as 02Aug2016).
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 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.