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;
... View more
