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;
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.
Try this:
file_received_dt= mdy(mon,day,year);
Try
file_received_dt=input(cats(day,mon,year),date9.);
Please supply some examples of the filenames you are trying to read.
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.