DATA Step, Macro, Functions and more

Extracting Year Month date from excel file name

Accepted Solution Solved
Reply
Contributor
Posts: 55
Accepted Solution

Extracting Year Month date from excel file name

[ Edited ]

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

 


Accepted Solutions
Solution
‎10-19-2016 08:46 AM
Super User
Posts: 5,076

Re: Extracting Year Month date from excel file name

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

View solution in original post


All Replies
Solution
‎10-19-2016 08:46 AM
Super User
Posts: 5,076

Re: Extracting Year Month date from excel file name

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 346 views
  • 0 likes
  • 2 in conversation