import multiple files using dates in a data set

[Ronein]

Hello

User define  month and year (YYMM) and then I create a data set that contain all dates in this year (char varaible with YYMMDD structure).

Task1-

The target is to import all excel tables of these days.

So in this example need to create  31  data sets (31 imports).

Task2-

Let's say that in some of the days the tables are not existing.

What is the way to do it in this case

%let CurMon=1907;
data daysWant;
 temp=input("&CurMon.01",yymmdd6.);
 do d=temp to intnx('month',temp,0,'e');
  date=put(d,yymmdd6.);output;
 end;
 keep date;
run;

/*Need to import all files of the dates in the daysWant data set*/
data Offers&YYMMDD.;
	infile "/path/Offers&YYMMDD." LRECL=54 recfm=f;
	input 
    ID     s370fzd8.
	OFFER     $ebcdic1.;
run;
 

[Ronein]

I know how to import it using this way.

The problem is what to do when the file is not exist (then need to skip the import of this file without getting error).

%let CurMon=1907;
data daysWant;
 temp=input("&CurMon.01",yymmdd6.);
 do d=temp to intnx('month',temp,0,'e');
  date=put(d,yymmdd6.);output;
 end;
 keep date;
run;


proc sql noprint;                              
 select date into :vector separated by '+'
 from daysWant;
quit;
%put &vector.;


%macro mmacro1; 
%do j=1 %to &k.;
%let YYMMDD=%scan(&vector1.,&j.,+);
data Offers&YYMMDD.;
	infile "/path/Offers&YYMMDD." LRECL=54 recfm=f;
	input 
    ID     s370fzd8.
	OFFER     $ebcdic1.;
run;
%end;
%mend mmacro1;
%mmacro1; 

[Ksharp]

You do not need to create a data "daysWant" .

The following could get you the file name under C:\TEMP . (<-- Change it as you wish)

data fname;
rc=filename('x','c:\temp');
did=dopen('x');
do i=1 to dnum(did);
  fname=dread(did,i);output;
end;
run;

[Ksharp]

Make a macro and call execute it.

%macro import(date=);
data Offers&DATE.;
	infile "/path/Offers&DATE." LRECL=54 recfm=f;
	input 
    ID     s370fzd8.
	OFFER     $ebcdic1.;
run;
%mend;


%let CurMon=1907;
data _null_;
 temp=input("&CurMon.01",yymmdd6.);
 do d=temp to intnx('month',temp,0,'e');
  date=put(d,yymmdd6.);
  call execute(cats('%import(date=',date,')'));
 end;
run;

[Ronein]

Hello,

thank you so much.

It is perfect and I succeeded  import 27 files but I get an error  that for some files it is not existing.

What is the way to import if the file exists?

What should I add to the code?

Your code is working perfect but just need to add the condition if  file exists

%let CurMon=1907;
%macro import(YYMMDD=);
data Hamlatzot&YYMMDD.;
	infile "/path/offers&YYMMDD." LRECL=54 recfm=f;
	input 
    ID     s370fzd8.;
run;
%mend import;

data _null_;
 temp=input("&CurMon.01",yymmdd6.);
 do d=temp to intnx('month',temp,0,'e');
  date=put(d,yymmdd6.); 
  call execute(cats('%import(YYMMDD=',date,')'));
 end;
run;

[Ksharp]

/* Use function FILEEXIT() to check the exist file*/

%macro import(YYMMDD=);

%if %sysfunc(fileexist( /usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.OP.CS.HMLATZOT.A&YYMMDD. )) %then %do;


data Hamlatzot&YYMMDD.;
	infile "/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.OP.CS.HMLATZOT.A&YYMMDD." LRECL=54 recfm=f;
	input 
    kodmivza     s370fzd8.
	sugmivza     $ebcdic1.
	snif   	   s370fzd3.
	lak_amt      s370fzd9.
	schum_mumlatz1 s370fzd8.
	degem1    s370fzd5.
	x         $20.;
	snif_amt=compress(put(snif,3.));
	HamlatzotDate=&YYMMDD.;
	Ind_Mumlatz_Ashrai_Click=1;
run;
%end;
%mend import;

/**** OR you could use the following code to get the exit files*******/


data fname;

 rc=filename('x','/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/'); 

did=dopen('x');

 do i=1 to dnum(did); 

fname=dread(did,i);output;

 end;

 run;

[Ronein]

Thanks.

I run for test only for one date.

When I run without  file exist statement  it works fine.

When I add  i get an error.

1 !+ input kodmivza s370fzd8. sugmivza $ebcdic1. snif s370fzd3. lak_amt s370fzd9. schum_mumlatz1
1 !+s370fzd8.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, /, ;, _DATA_, _LAST_, _NULL_.

ERROR 200-322: The symbol is not recognized and will be ignored.

2 + degem1 s370fzd5. x $20.; snif_amt=compress(put(snif,3.)); HamlatzotDate=190701;
Ind_Mumlatz_Ashrai_Click=1; run;

ERROR: No DATALINES or INFILE statement.
ERROR: Extension for physical file name "/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.OP.CS.HMLATZOT.A190701" does not

%let CurMon=1907;
%macro import(YYMMDD=);
%if %sysfunc(fileexist(/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.OP.CS.HMLATZOT.A&YYMMDD. )) %then
data Offers&YYMMDD.;
	infile "/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.OP.CS.HMLATZOT.A&YYMMDD." LRECL=54 recfm=f;
	input 
    kodmivza     s370fzd8.
	sugmivza     $ebcdic1.
	snif   	   s370fzd3.
	lak_amt      s370fzd9.
	schum_mumlatz1 s370fzd8.
	degem1    s370fzd5.
	x         $20.;
	snif_amt=compress(put(snif,3.));
	HamlatzotDate=&YYMMDD.;
	Ind_Mumlatz_Ashrai_Click=1;
run;
%mend import;
data _null_;
 temp=input("&CurMon.01",yymmdd6.);
 do d=temp to temp;
  date=put(d,yymmdd6.); 
  call execute(cats('%import(YYMMDD=',date,')'));
 end;
run;

[Kurt_Bremser · Accepted solution]

---

@Ronein wrote:

Thanks.

I run for test only for one date.

When I run without  file exist statement  it works fine.

When I add  i get an error.

1 !+ input kodmivza s370fzd8. sugmivza $ebcdic1. snif s370fzd3. lak_amt s370fzd9. schum_mumlatz1
1 !+s370fzd8.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, /, ;, _DATA_, _LAST_, _NULL_.

ERROR 200-322: The symbol is not recognized and will be ignored.

2 + degem1 s370fzd5. x $20.; snif_amt=compress(put(snif,3.)); HamlatzotDate=190701;
Ind_Mumlatz_Ashrai_Click=1; run;

ERROR: No DATALINES or INFILE statement.
ERROR: Extension for physical file name "/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.OP.CS.HMLATZOT.A190701" does not

%let CurMon=1907;
%macro import(YYMMDD=);
%if %sysfunc(fileexist(/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.OP.CS.HMLATZOT.A&YYMMDD. )) %then
data Offers&YYMMDD.;
	infile "/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.OP.CS.HMLATZOT.A&YYMMDD." LRECL=54 recfm=f;
	input 
    kodmivza     s370fzd8.
	sugmivza     $ebcdic1.
	snif   	   s370fzd3.
	lak_amt      s370fzd9.
	schum_mumlatz1 s370fzd8.
	degem1    s370fzd5.
	x         $20.;
	snif_amt=compress(put(snif,3.));
	HamlatzotDate=&YYMMDD.;
	Ind_Mumlatz_Ashrai_Click=1;
run;
%mend import;
data _null_;
 temp=input("&CurMon.01",yymmdd6.);
 do d=temp to temp;
  date=put(d,yymmdd6.); 
  call execute(cats('%import(YYMMDD=',date,')'));
 end;
run;

---

You have a mistake here:

%if %sysfunc(fileexist(/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.OP.CS.HMLATZOT.A&YYMMDD. )) %then
data Offers&YYMMDD.;

The macro %if statement ends with the semicolon after the &YYMMDD. reference, so it will resolve to this data step code:

data Offers1907

(note the missing semicolon!)

immediately followed by

	infile "/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.OP.CS.HMLATZOT.A&YYMMDD." LRECL=54 recfm=f;

so you get this syntactically wrong statement:

data Offers1907
	infile "/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.OP.CS.HMLATZOT.A&YYMMDD." LRECL=54 recfm=f;

You need to use a %do-%end block:

%let CurMon=1907;
%macro import(YYMMDD=);
%if %sysfunc(fileexist(/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.OP.CS.HMLATZOT.A&YYMMDD. ))
%then %do;
data Offers&YYMMDD.;
	infile "/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.OP.CS.HMLATZOT.A&YYMMDD." LRECL=54 recfm=f;
	input 
    kodmivza     s370fzd8.
	sugmivza     $ebcdic1.
	snif   	   s370fzd3.
	lak_amt      s370fzd9.
	schum_mumlatz1 s370fzd8.
	degem1    s370fzd5.
	x         $20.;
	snif_amt=compress(put(snif,3.));
	HamlatzotDate=&YYMMDD.;
	Ind_Mumlatz_Ashrai_Click=1;
run;
%end;
%mend import;