topic Re: import if exits in SAS Programming

import if exits

Ronein — Thu, 24 Jun 2021 12:26:00 GMT

Hello

I want to import txt files if they exists between 20JUN2021 till today (24JUN2021).

Then I want to put them in stack structure (SET them together).

I see that actually the files that exists are :20JUN2021,21JUN2021, 22JUN2021,23JUN2021.

My question:

In the output data set I dont see data from 20JUN2021.

Why?

What is the problem in the code that data of 20JUN2021 is not imported?

%let start_date=20JUN2021; 
%let end_date=%sysfunc(today(),date9.);


%macro import_one(YYYYMMDD);
%if %sysfunc(fileexist(/path/ttt&YYYYMMDD..txt))
%then %do;
data DailyPanel_a;
infile "/path/ttt&YYYYMMDD."
dlm=';' dsd truncover firstobs=2;
input VAR1-VAR59;
informat VAR1-VAR59 best32. ;
format VAR1-VAR59 best12. ;
run;
%end;
%else %do;
data DailyPanel_a;
informat VAR1-VAR59 best32. ;
format VAR1-VAR59 best12. ;
stop;
run;
%end;
proc append data=DailyPanel_aa base=Joe.DailyPanel_All  force; run; quit;
%mend import_one;


data datestbl(keep=YYYYMMDD);
date="&start_date"d;
YYYYMMDD=put(date,yymmddn8.);
do while (date<="&end_date"d);  
output;  
date=intnx('day', date, 1, 's');    
YYYYMMDD=put(date,yymmddn8.);
call execute('%nrstr(%import_one('!!YYYYMMDD!!'))');
end;
run;

Re: import if exits

Tom — Thu, 24 Jun 2021 12:53:50 GMT

How are you testing if the data to that particular date is in the file?

Perhaps you should include the date as a variable in the dataset you are generating from the text file.

So here is your %IMPORT_ONE() macro cleaned up a little. It will add a variable DATE so you know which file the observations came from. If gets rid of the useless INFORMAT and FORMAT statements. It also always makes the temporary dataset and always runs the PROC APPEND. But when the file does not exist it makes an empty daily dataset.

%macro import_one(YYYYMMDD);
data DailyPanel_a;
  length date var1-var59 8;
  date = input("&yyyymmdd",yymmdd8.);
  format date date9.;
%if %sysfunc(fileexist(/path/ttt&YYYYMMDD..txt)) %then %do;
  infile "/path/ttt&YYYYMMDD." dlm=';' dsd truncover firstobs=2;
  input VAR1-VAR59;
%end;
%else %do;
  stop;
%end;
run;
%end;
proc append data=DailyPanel_aa base=Joe.DailyPanel_All force; 
run; 
%mend import_one;

Now let's also simplify the data step that is generating one macro call per date:

data datestbl;
  do date="&start_date"d to "&end_date"d;
    call execute(cats('%nrstr(%import_one)(',put(date,yymmddn8.),')'));
    output;
  end;
  format date date9.;
run;

If you want there to be a record in the final aggregate table when the text file does not exist for that date you can remove the STOP statement in the macro. But if the file exists but it has zero observations you will still not get any records for that date.

Re: import if exits

PaigeMiller — Thu, 24 Jun 2021 12:59:37 GMT

I have modified your code for %IMPORT_ONE so we can all see what is happening (and because I don't have your data sets).

%let start_date=20JUN2021; 
%let end_date=%sysfunc(today(),date9.);

%macro import_one(date);
    %put &=date;
%mend;

data _null_;
    date="&start_date"d;
    YYYYMMDD=put(date,yymmddn8.);
    do while (date<="&end_date"d);  
        date=intnx('day', date, 1, 's');    
        YYYYMMDD=put(date,yymmddn8.);
        call execute('%nrstr(%import_one('!!YYYYMMDD!!'))');
    end;
run;

This produces in the LOG:

1    + %import_one(20210621)
DATE=20210621
2    + %import_one(20210622)
DATE=20210622
3    + %import_one(20210623)
DATE=20210623
4    + %import_one(20210624)
DATE=20210624
5    + %import_one(20210625)
DATE=20210625

So, the data step that produces CALL EXECUTE statements is not producing the desired set of calls to %IMPORT_ONE. Why? Because you have placed CALL EXECUTE after the date is incremented by 1. So the first date it produces in 20210621 and also it produces 20210625 (which is tomorrow).

If you move CALL EXECUTE before you increment the date by 1, then you get what you want.

data _null_;
    date="&start_date"d;
    YYYYMMDD=put(date,yymmddn8.);
    do while (date<="&end_date"d);  
        call execute('%nrstr(%import_one('!!YYYYMMDD!!'))');
        date=intnx('day', date, 1, 's');    
        YYYYMMDD=put(date,yymmddn8.);
    end;
run;

This type of debugging, which allows you to LOOK AT what is happening, is something you should be doing yourself. It's not clear if you are doing this.

Please, in the future, make your coding and debugging easier (and also make it easier for all of us) by indenting your code, as I have done.