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