topic Re: import multiple files using dates in a data set in SAS Programming

import multiple files using dates in a data set

Ronein — Sun, 29 Dec 2019 10:32:34 GMT

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;

Re: import multiple files using dates in a data set

Ronein — Sun, 29 Dec 2019 10:37:56 GMT

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;

Re: import multiple files using dates in a data set

Ksharp — Sun, 29 Dec 2019 10:40:15 GMT

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;

Re: import multiple files using dates in a data set

Ksharp — Sun, 29 Dec 2019 10:44:16 GMT

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;

Re: import multiple files using dates in a data set

Ronein — Sun, 29 Dec 2019 11:09:02 GMT

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;

Re: import multiple files using dates in a data set

Ksharp — Sun, 29 Dec 2019 11:18:44 GMT


/* 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;

Re: import multiple files using dates in a data set

Ronein — Sun, 29 Dec 2019 11:35:32 GMT

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;

Re: import multiple files using dates in a data set

Kurt_Bremser — Sun, 29 Dec 2019 11:50:29 GMT

@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;