Import If file exist %if %sysfunc(fileexist

[Ronein]

Hello

When I run the following code  it create successfully a data set

DATA Want(where=(cust_type=1 AND ind_pop IN(0,81,83)));
INFILE "/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.RT.HAMLTZOT.A&last_business_Day_YYYYMMDD."
firstobs=2;
INPUT 
@1  tar 8. 
@10 bank 2. 
@13 snif 3. 
@17 lak_id 9. 
@27 cust_type 1. 
@29 heshbon 9. 
@39 engine_ind 1.
@41 hamlatza 8. 
@50 degem_auto $14. 
@151 degem_snif $14
@252 lak_0 1.
@254 ind_pop 2.
@257 arvut_hadadit 1. 
@259 ind_car_loan 1. ;
RUN;

But when I add the condition that check firstly if file exist and import only if file exists then nothing happened and the required data set is not created

%macro Import_Leumi_Hanitoy; 
%if %sysfunc(fileexist("/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.RT.HAMLTZOT.A&last_business_Day_YYYYMMDD."))%then %do;
DATA Want(where=(cust_type=1 AND ind_pop IN(0,81,83)));
INFILE "/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.RT.HAMLTZOT.A&last_business_Day_YYYYMMDD."
firstobs=2;
INPUT 
@1  tar 8. 
@10 bank 2. 
@13 snif 3. 
@17 lak_id 9. 
@27 cust_type 1. 
@29 heshbon 9. 
@39 engine_ind 1.
@41 hamlatza 8. 
@50 degem_auto $14. 
@151 degem_snif $14
@252 lak_0 1.
@254 ind_pop 2.
@257 arvut_hadadit 1. 
@259 ind_car_loan 1. ;
RUN;
%end;
%mend Import_Leumi_Hanitoy;

Here is the Log

1                                                          The SAS System                              13:33 Wednesday, May 29, 2024

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='בקרת קיום קבצים יומיים_ובדיקה_ש913_מכיל_0_שורות_C';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='K:\רון_ענת_גיבוי_תוכניות_יומיות\בקרת_קיום_קבצים_יומיים\בקרת קיום קבצים
8        ! יומיים_ובדיקה_ש913_מכיל_0_שורות_C.sas';
9          %LET _SASPROGRAMFILEHOST='VST1H103A2028';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HTMLBlue
17             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         %macro Import_Leumi_Hanitoy;
27         %if
27       ! %sysfunc(fileexist("/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.RT.HAMLTZOT.A&last_business_Day_YYYYMMDD."))%then
27       !  %do;
28         DATA Want(where=(cust_type=1 AND ind_pop IN(0,81,83)));
29         INFILE "/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.RT.HAMLTZOT.A&last_business_Day_YYYYMMDD."
30         firstobs=2;
31         INPUT
32         @1  tar 8.
33         @10 bank 2.
34         @13 snif 3.
35         @17 lak_id 9.
36         @27 cust_type 1.
37         @29 heshbon 9.
38         @39 engine_ind 1.
39         @41 hamlatza 8.
40         @50 degem_auto $14.
41         @151 degem_snif $14
42         @252 lak_0 1.
43         @254 ind_pop 2.
44         @257 arvut_hadadit 1.
45         @259 ind_car_loan 1. ;
46         RUN;
47         %end;
48         %mend Import_Leumi_Hanitoy;
49         
50         
51         GOPTIONS NOACCESSIBLE;
52         %LET _CLIENTTASKLABEL=;
53         %LET _CLIENTPROCESSFLOWNAME=;
54         %LET _CLIENTPROJECTPATH=;
2                                                          The SAS System                              13:33 Wednesday, May 29, 2024

55         %LET _CLIENTPROJECTPATHHOST=;
56         %LET _CLIENTPROJECTNAME=;
57         %LET _SASPROGRAMFILE=;
58         %LET _SASPROGRAMFILEHOST=;
59         
60         ;*';*";*/;quit;run;
61         ODS _ALL_ CLOSE;
62         
63         
64         QUIT; RUN;
65         

What is the problem ?

Why the data set is not created while the file to import exist?

Please note that macro var &last_business_Day_YYYYMMDD.  has value  20240527

[rudfaden]

Are you running %Import_Leumi_Hanitoy?

[Kurt_Bremser]

You define the macro, but you never call it.

[Ronein]

I will come back home and check if I didn't run the macro

[Quentin]

You could also add to the macro, to give you a message in the log if the file doesn't exist, e.g.:

%if %sysfunc(fileexist("/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.RT.HAMLTZOT.A&last_business_Day_YYYYMMDD."))%then %do;
  ...
%end;
%else %put NOTE: (USER) the input file /usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.RT.HAMLTZOT.A&last_business_Day_YYYYMMDD. does not exist, skipping load process. ;

[Kurt_Bremser]

---

@Ronein wrote:
I will come back home and check if I didn't run the macro

---

Just look at the log you posted. No macro call there.

44         @257 arvut_hadadit 1.
45         @259 ind_car_loan 1. ;
46         RUN;
47         %end;
48         %mend Import_Leumi_Hanitoy;
49         
50         
51         GOPTIONS NOACCESSIBLE;
52         %LET _CLIENTTASKLABEL=;
53         %LET _CLIENTPROCESSFLOWNAME=;
54         %LET _CLIENTPROJECTPATH=;

The GOPTIONS is part of the automatic code sent after each submission.

[yabwon]

Besides the fact it looks you didn't call the macro to run it, I have one more (rough) remark.

The way you wrote the macro - I consider such type of writing macros a bad programming practice. 

You have macro without parameters which is dependent on some "I-do-not-know-where" defined macrovariables (i.e. last_business_Day_YYYYMMDD) and in production setup such macro is very poorly reusable.

Though my remark may sound harsh, the remark is given with a good faith and to make your life easier in the future.

If I were you first of all I would rewrote your code like this:

%macro Import_Leumi_Hanitoy(path_to_file); 

Filename F "&path_to_file.";

%if %sysfunc(fexist(f))%then 
%do;
  DATA Want(where=(cust_type=1 AND ind_pop IN(0,81,83)));
    INFILE F
    firstobs=2;
    INPUT 
    @1  tar 8. 
    @10 bank 2. 
    @13 snif 3. 
    @17 lak_id 9. 
    @27 cust_type 1. 
    @29 heshbon 9. 
    @39 engine_ind 1.
    @41 hamlatza 8. 
    @50 degem_auto $14. 
    @151 degem_snif $14
    @252 lak_0 1.
    @254 ind_pop 2.
    @257 arvut_hadadit 1. 
    @259 ind_car_loan 1. ;
  RUN;
%end;

filename f clear;
%mend Import_Leumi_Hanitoy;


%Import_Leumi_Hanitoy(/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.RT.HAMLTZOT.A&last_business_Day_YYYYMMDD.)

to make it:

1) less error prone (only one place where path to file is provided),

2) if you decide to change the file location you won't have to rewrite the macro, just a call to it

3) with that version, your macro will be required to run only with macrovariables provided as paramenters, and not some "unknown-where-defined" macrovariables.

All the best

Bart

[Tom]

Once you define the macro you need to call it.

%macro Import_Leumi_Hanitoy; 
%if %sysfunc(fileexist("/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.RT.HAMLTZOT.A&last_business_Day_YYYYMMDD."))%then %do;
DATA Want(where=(cust_type=1 AND ind_pop IN(0,81,83)));
INFILE "/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.RT.HAMLTZOT.A&last_business_Day_YYYYMMDD."
firstobs=2;
INPUT 
@1  tar 8. 
@10 bank 2. 
@13 snif 3. 
@17 lak_id 9. 
@27 cust_type 1. 
@29 heshbon 9. 
@39 engine_ind 1.
@41 hamlatza 8. 
@50 degem_auto $14. 
@151 degem_snif $14
@252 lak_0 1.
@254 ind_pop 2.
@257 arvut_hadadit 1. 
@259 ind_car_loan 1. 
;
RUN;
%end;
%mend Import_Leumi_Hanitoy;
%Import_Leumi_Hanitoy;

But you do not need to define a macro just to do a simple %IF/%THEN?%DO block anymore.

%if %sysfunc(fileexist("/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.RT.HAMLTZOT.A&last_business_Day_YYYYMMDD."))%then %do;
DATA Want(where=(cust_type=1 AND ind_pop IN(0,81,83)));
INFILE "/usr/local/SAS/MidulOld/score_cs/MF/MF2NT/BLL.B2.IF.RT.HAMLTZOT.A&last_business_Day_YYYYMMDD."
firstobs=2;
INPUT 
@1  tar 8. 
@10 bank 2. 
@13 snif 3. 
@17 lak_id 9. 
@27 cust_type 1. 
@29 heshbon 9. 
@39 engine_ind 1.
@41 hamlatza 8. 
@50 degem_auto $14. 
@151 degem_snif $14
@252 lak_0 1.
@254 ind_pop 2.
@257 arvut_hadadit 1. 
@259 ind_car_loan 1. 
;
RUN;
%end;

[Ksharp]

Make sure fileexist() return 1 :