Hello
I need to import many txt files into sas
The files exists almost every day and I need to import files for each month in dates : 02,05,10,20,25
The name of the tables is looking like that: tblYYMMDD
so for example:
From Janunary 2019 I need to import files:
tbl190102
tbl190105
tbl190110
tbl190120
tbl190125
The problem is that sometimes the file doesn't exist and then I need to import a file from one day after (and if not exists then 2 days after....etc)
For example:
bl190105 is not existing so I need to import bl190106
tbl190125 is not existing and also tbl190126 is not exisiting so i need to import tbl190127
Please find the basic code .
I need to find solution for 2 problems:
1-How to make more efficient code that create the dates that need to import (instead of the long %let that I created)
2-How to tell SAS that if the file doesn't exist then need to import 1 day after and if 1 day after doen't exist then 2 days after ...(maximum 5 days later....)
%let vector1=190102+190105+190110+190115+190120+190125
+190202+190205+190210+190215+190220+190225
+190302+190305+190310+190315+190320+190325
+190402+190405+190410+190415+190420+190425
+190502+190505+190510+190515+190520+190525
+190602+190605+190610+190615+190620+190625
+190702+190705+190710+190715+190720+190725
+190802+190805+190810+190815+190820+190825
+190902+190905+190910+190915+190920+190925
+191002+191005+191010+191015+191020+191025
+191102+191105+191110+191115+191120+191125;
%let k=%sysfunc(countw(&vector1));
%put &k.;
%macro mmacro1;
%do j=1 %to &k.;
%let YYMMDD=%scan(&vector1.,&j.,+);
data Hamlatzot&YYMMDD.;
infile "Path.tbl&YYMMDD." LRECL=54 recfm=f;
input
X1 s370fzd8.
X2 $ebcdic1.
X3 s370fzd3.
X4 s370fzd9.
X5 s370fzd8.
X6 s370fzd5.
X7 $20.;
run;
%end;
%mend;
%mmacro1;
Please have a look at this:
%macro mmacro1(YYMMDD=);
data Hamlatzot&YYMMDD.;
infile "Path.tbl&YYMMDD." LRECL=54 recfm=f;
input
X1 s370fzd8.
X2 $ebcdic1.
X3 s370fzd3.
X4 s370fzd9.
X5 s370fzd8.
X6 s370fzd5.
X7 $20.
;
run;
%mend;
%let month = 1;
%let year = 2019;
data _null_;
length name $ 20 date 8;
format date yymmddn6.;
do day = 2,5,10,20,25;
date = mdy(&month., day, &year.);
name = cats('tbl', vvalue(date));
do while (not fileexist(cats("&path/", name)));
date = date + 1;
name = cats('tbl', vvalue(date));
end;
call execute(cats('%nrstr(%mmacro1(yymmdd=', vvalue(date), '))'));
end;
run;
The outer-loop in the data step tries to find a file as close as possible to the days you named. As soon as a file is found, your macro is called via call execute.
Please have a look at this:
%macro mmacro1(YYMMDD=);
data Hamlatzot&YYMMDD.;
infile "Path.tbl&YYMMDD." LRECL=54 recfm=f;
input
X1 s370fzd8.
X2 $ebcdic1.
X3 s370fzd3.
X4 s370fzd9.
X5 s370fzd8.
X6 s370fzd5.
X7 $20.
;
run;
%mend;
%let month = 1;
%let year = 2019;
data _null_;
length name $ 20 date 8;
format date yymmddn6.;
do day = 2,5,10,20,25;
date = mdy(&month., day, &year.);
name = cats('tbl', vvalue(date));
do while (not fileexist(cats("&path/", name)));
date = date + 1;
name = cats('tbl', vvalue(date));
end;
call execute(cats('%nrstr(%mmacro1(yymmdd=', vvalue(date), '))'));
end;
run;
The outer-loop in the data step tries to find a file as close as possible to the days you named. As soon as a file is found, your macro is called via call execute.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.