Import using call excute and create series of dates

[Ronein]

Hello

I want to import CSV files into SAS of specific dates .

IF the file doesnt exist then I dont want to get error.

If the file doesnt exist then I want to create empty data set.

Instead of typing the dates manually ,I want to create a series of dates between 01JAN2020 till today.

What is the way to do it together with call execute?

%macro import_one(date);
%if %sysfunc(fileexist(path/revenue&date..csv))
%then %do;
data revenue&date.;
infile "/path/revenue&date..csv))" dlm=',' dsd truncover;
length /* insert variable definitions here */;
input /* variable list as above */;
run;
%end;
%else %do;
data revenue&date.;
length /* same as above */;
run;
%end;
%mend;

Data datestbl;
input dates $;
call execute('%nrstr(%import_one('!!date!!'))');
cards;
20210603
20210601
20210528
20210514
;

[Tom]

You did not add the details of how to read the rows from the CSV file.  Replace the comment blocks with the actual information to compete the LENGTH and INPUT statements.   Add any FORMAT statements or INFORMAT statements that are NEEDED (most variables do not need either an informat or a format attached to them).

Do you not KNOW the names and types of the variables you need to read from each file? 

If not you could try running PROC IMPORT on one of them and see what names it finds.  I would not recommend copying the code it generates since it overly complicated, but you could use it a a guide for how to define the variables.

Do the files not have names in the first row?  Your current code is not skipping the first row of the CSV file.

You could move the %IF statement so that it is just around the part that reads from the file to simply the program and not have to define the same set of variables in two places.

%macro import_one(date);
%local filename ;
%let filename=/path/revenue&date..csv;

data revenue&date.;
  length /* insert variable definitions here */;
%if %sysfunc(fileexist(&filename)) %then %do;
  infile "&filename" dlm=',' dsd truncover;
  input /* variable list as above */;
%end;
%else %do;
  stop;
%end;
run;
%mend import_one;

data datestbl;
  input dates $;
  call execute(cats('%nrstr(%import_one)(',dates,')'));
cards;
20210603
20210601
20210528
20210514
;

[PhilC]

When I do this it looks like this.  (check me, I didn't run this code.)  Although, I don't ever do this with 400 EXECUTEs.  Something tells me this wont work because I think there's a maximum limit on the number of EXECUTEs.  Something I read in these boards.

Data _Null_;
  input date $;
/*psuedo code, I didn't test this */
  length cmd $30;
    cmd=cat('%import_one(',date,');');
  call execute(cmd);
cards;
20210603
20210601
20210528
20210514
;
run;

[PhilC]

Related: Macro Error when not limiting number of observatio... - SAS Support Communities 

Ok maybe you need to call more "CALL EXECUTE"s before it becomes a problem.

[Tom]

---

@PhilC wrote:

Related: Macro Error when not limiting number of observatio... - SAS Support Communities 

Ok maybe you need to call more "CALL EXECUTE"s before it becomes a problem.

---

Using %NRSTR() to prevent the macro from running while the call is being pushed on the stack to execute later. That will cut down on the risk of overflowing the stack.

But this is just another reason to simply write the code to a file and then %INCLUDE the file instead of using call execute.

filename code temp;
data _null_;
  input date $;
  file code;
  put '%import_one(' date ')' ;
cards;
20210603
20210601
20210528
20210514
;
%include code / source2;

[Reeza]

---

@PhilC wrote:

When I do this it looks like this.  (check me, I didn't run this code.)  Although, I don't ever do this with 400 EXECUTEs.  Something tells me this wont work because I think there's a maximum limit on the number of EXECUTEs.  Something I read in these boards.

Do you have a reference for this? I checked the previous link and it doesn't really deal with the issue IMO. 

I just ran a small test with over 1000 call executes and regularly relied on it for large runs. Never run into any issues except full logs or diskspace but that's a very easy fix with PROC PRINTTO to redirect a log and PROC DATASETS to clean up after each macro run. 

%macro summary(dsn= , index= );

ods select none;
proc means data=&dsn N MEAN stackods;
ods output summary = summary&index.;
run;
ods select all;

%mend;


%summary(dsn=sashelp.class, index=1);

data _null_;
do i=1 to 1000;
call execute(cats('%summary(dsn=sashelp.class, index=', i, ');'));
end;
run;

[Kurt_Bremser]

I put the comments into this code not because I am extremely bored, but FOR A REASON!

You must insert your definitions in the LENGTH statements (both!), and the variable names in the INPUT.

If you have special values (dates, amounts with currency, percentages, numbers with commas), also add an INFORMAT for those.

If the code does not work then as expected, please post the log.

[Ronein]

Thanks,

If all varaibles are numeric ,should I also add LENGTH statement? 

what should I write there if all varaibles are numeric?

LENGTH Var-Var25 8.;

[Kurt_Bremser]

The dot is needed for formats, lengths do not need it.

length Var-Var25 8;