BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jayakumarmm
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

1 REPLY 1
Astounding
PROC Star

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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