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 am macro date variable is always converted to 01/01/1960, In the below Received_date is the variable where I am tryng to assign the macro variable. &file_dt

 

filename fnames pipe 'ls /usr/path/*.xlsx';


data fnames;
    infile fnames pad missover;
    input @1 filename $255.;
	year = scan(filename, -2);
	day = scan(filename, -3);
	mon = substr(scan(filename, -4),1,3);
	con = catx("/",day,mon,year);
   file_received_dt=input(catx("/",day,mon,year),date11.);
   format file_received_dt mmddyy10.;
    n=_n_;
run;

proc sql noprint; select count(filename) into :num from fnames; quit;

%macro file_process;
    %do i=3 %to #

        proc sql noprint;
            select strip(filename),file_received_dt into :filename, :file_received_dt  from fnames where n=&i;
        quit;

	%let file_proc = &filename;
	%let file_dt = &file_received_dt;

	libname xlsFile XLSX "&file_proc";
	options validvarname=v7;
	options SYMBOLGEN MPRINT;
	
	PROC SQL;
    	create table  work.data_raw as 
	(select * from xlsFile.'Sheet1$A3:AD2000'n where col1 is not null);
	quit;
	data work.data(rename=(number=number_1));
	set work.data_raw;
	if find(dob,".") Then 
	do;
	dob = substr(dob,1,6);
	end;
	if length(dob) >= 10 Then 
	do;
	format dob_Dt mmddyy10.;
	dob_Dt = dob;
	end;
	else
	do;
	dob_Dt = substr(dob,1,6) - 21916;
	 format dob_Dt mmddyy10.; 
	end;
	format Load_dt mmddyy10.;
	format Received_dt mmddyy10.;
	data_dt = put((INTNX('month', %sysfunc(today()), -2, 'B')),monyy5.);
	Load_dt = Today();

	Received_dt = &file_dt;

	sam_mod = put(sam, $8.);
	track_num_mod = input(col1, 8.);
	run;

	data work.data1(drop= col1 dob);
	set work.data;
	run;

	PROC SQL;
    	create table  work.data_raw_2 as 
	(select * from xlsFile.'transmittal tab$A3:L2000'n);
	quit;
	data work.data2;
	set work.data_raw_2;
	format Load_dt mmddyy10.;
	format Received_dt mmddyy10.;
	data_dt = put((INTNX('month', %sysfunc(today()), -2, 'B')),monyy5.);
	Load_dt = Today();

	Received_dt =&file_dt;

	run;
    %end;
%mend;

%file_process;
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Before I'd go ANY farther with this, I'd make reasonable filenames mandatory. Anybody who uses a date format like that in an IT context is in dire need of being the target of a LART. And get rid of the stupid blanks, there's underlines for that.

 

As it is, I'd first do

filename = scan(filename,1,'.');

to remove the extension.

Then

year = input(scan(filename,-1,' '),4.);
day = input(scan(filename,-2,' '),2.);

For the month, I'd create a custom infomat that converts the textual month names to the numbers:

proc format;
invalue inmonth
  'January' = 1
  'February' = 2
....
  'September' = 9
...
;
run;

You can then use that in

month = input(scan(filename,-3,' '),inmonth.);

After that, use the mdy() function to build a date.

All that would not be necessary if some &?%!?# had used a yyyy-mm-dd format for the date in the filename.

View solution in original post

7 REPLIES 7
SASKiwi
PROC Star

Try this:

 

file_received_dt= mdy(mon,day,year);
jayakumarmm
Quartz | Level 8
I have tried this but it is not working.I am able to get the exact value in macro variable &file_dt with my earlier code itself but when I try assign to a variable (Received_dt) it is breaking.
ballardw
Super User

Try

 

file_received_dt=input(cats(day,mon,year),date9.);

SASKiwi
PROC Star

Please supply some examples of the filenames you are trying to read.

jayakumarmm
Quartz | Level 8
Filenames are like "log response September 20 2016.xlsx"
Kurt_Bremser
Super User

Before I'd go ANY farther with this, I'd make reasonable filenames mandatory. Anybody who uses a date format like that in an IT context is in dire need of being the target of a LART. And get rid of the stupid blanks, there's underlines for that.

 

As it is, I'd first do

filename = scan(filename,1,'.');

to remove the extension.

Then

year = input(scan(filename,-1,' '),4.);
day = input(scan(filename,-2,' '),2.);

For the month, I'd create a custom infomat that converts the textual month names to the numbers:

proc format;
invalue inmonth
  'January' = 1
  'February' = 2
....
  'September' = 9
...
;
run;

You can then use that in

month = input(scan(filename,-3,' '),inmonth.);

After that, use the mdy() function to build a date.

All that would not be necessary if some &?%!?# had used a yyyy-mm-dd format for the date in the filename.

Kurt_Bremser
Super User

Look at the log at check for messages concerning automatic type conversion, missing values and the like.

 

Add put and %put statements at the crucial points in your code to verify that data step and macro variables contain exactly what you expected.

Be aware that a put in a data step with lots of iterations/observation might fatally swamp your log, so test first with a small sample set.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 7 replies
  • 3961 views
  • 0 likes
  • 4 in conversation