Execute a set of procedures "X" times. Each time change to the next number (file)

[OscarUvalle]

Hi there,

I have a set of procedures or steps that runs in a row, the steps mainly consist in:

1. Importing a file using proc import

2. Reshaping the file using a data set step.

3. Filtering data using data step

4. Export the file using proc export

5. Rename last table used. 

Then start again with the next file. 

So far, I can do it manually, just changing the number of the file from 1 to two and then 3 and so on. 

%let filenumber and l%et tablename = _1 

I looking a way to do it automatically, If I have 50 files run everything 50 times for each file. I am not sure if I just need a do loop, or define a macro or maybe something else. I have also a macro that stops if an error appears in any step.

Can anyone point me out who to sort this problem out?. My fingers will really appreciate it.  

The files are stored with numbers basically, therefore files are named from 1 to 50.

%let odata = _gf_income; * data name for importing first time ;
%let ldata = _long_gf_income; * data name where data is transform to long format ;
%let final = _final_gf_income; * final version containing just london stock exchange companies ;
%let filenumber = 1 ;
%let tablename = _1 ;
%let all_tables = all_incomestatements;
%macro check_for_errors;
   %if &syserr > 0 %then %do;
      %abort cancel;
   %end;
%mend check_for_errors;

[ballardw]

---

@OscarUvalle wrote:

Hi there,

I have a set of procedures or steps that runs in a row, the steps mainly consist in:

1. Importing a file using proc import

2. Reshaping the file using a data set step. Almost certain to fail sometimes as Proc import could guess differently for each file and code written to reshape one data set may fail on another.

3. Filtering data using data step

4. Export the file using proc export

5. Rename last table used. 

Then start again with the next file. 

So far, I can do it manually, just changing the number of the file from 1 to two and then 3 and so on. 

%let filenumber and l%et tablename = _1 

I looking a way to do it automatically, If I have 50 files run everything 50 times for each file. I am not sure if I just need a do loop, or define a macro or maybe something else. I have also a macro that stops if an error appears in any step.

Can anyone point me out who to sort this problem out?. My fingers will really appreciate it.  

The files are stored with numbers basically, therefore files are named from 1 to 50.

%let odata = _gf_income; * data name for importing first time ;
%let ldata = _long_gf_income; * data name where data is transform to long format ;
%let final = _final_gf_income; * final version containing just london stock exchange companies ;
%let filenumber = 1 ;
%let tablename = _1 ;
%let all_tables = all_incomestatements;
%macro check_for_errors;
   %if &syserr > 0 %then %do;
      %abort cancel;
   %end;
%mend check_for_errors;

---

If these files are supposed to have the same format then write a data step that will work to read all of the data files. Otherwise with proc import guessing as to variable type and length, and possibly even variable names any data step code to manipulate the result for your second and third steps has a high probability of failing.

If the data files are actually spread sheet the failure rate will go way up.

[Reeza]

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

1. Develop the process for one file. 

2. Convert to a macro for all files

The tutorial above explains that concept. 

As someone else has already mentioned, you could just read the file once, and use BY group processing to do it all at once. This is by far the most efficient method. 

1. Write a data step to read all the files at once (assuming not Excel files, otherwise you need the process above).

2. Change process to use BY groups

3. Export to individual files

---

@OscarUvalle wrote:

Hi there,

I have a set of procedures or steps that runs in a row, the steps mainly consist in:

1. Importing a file using proc import

2. Reshaping the file using a data set step.

3. Filtering data using data step

4. Export the file using proc export

5. Rename last table used. 

Then start again with the next file. 

So far, I can do it manually, just changing the number of the file from 1 to two and then 3 and so on. 

%let filenumber and l%et tablename = _1 

I looking a way to do it automatically, If I have 50 files run everything 50 times for each file. I am not sure if I just need a do loop, or define a macro or maybe something else. I have also a macro that stops if an error appears in any step.

Can anyone point me out who to sort this problem out?. My fingers will really appreciate it.  

The files are stored with numbers basically, therefore files are named from 1 to 50.

%let odata = _gf_income; * data name for importing first time ;
%let ldata = _long_gf_income; * data name where data is transform to long format ;
%let final = _final_gf_income; * final version containing just london stock exchange companies ;
%let filenumber = 1 ;
%let tablename = _1 ;
%let all_tables = all_incomestatements;
%macro check_for_errors;
   %if &syserr > 0 %then %do;
      %abort cancel;
   %end;
%mend check_for_errors;

---

[SASKiwi]

Here are my suggestions:

Run your SAS programs as a batch job with the ERRORABEND option then you won't need an error checking macro.

Write a DATA step to construct a %INCLUDE statement to read in and run your series of procedures using CALL EXECUTE. This way you can avoid macros entirely. It would look something like this:

data _null_;
  do Loop_Count = 1 to 50;
    exec_stmt = '%let filenumber = ' !! put(Loop_Count, 2.) !! 
                '; %include "MySASProgramFolder\MySASProgram.sas";';
    call execute('%nrstr(' !! exec_stmt !! ')');
  end;
run;