Extracting Year Month date from excel file name

jayakumarmm — Mon, 17 Oct 2016 16:04:27 GMT

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

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

Re: Extracting Year Month date from excel file name

Astounding — Mon, 17 Oct 2016 16:21:02 GMT

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

topic Extracting Year Month date from excel file name in SAS Programming

Extracting Year Month date from excel file name

Re: Extracting Year Month date from excel file name