Extracting Year Month date from excel file name

[jayakumarmm]

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;;

[Astounding · Accepted solution]

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).