How to perform same SAS procedure over multiple datasets

Question

I put together a proc IML code that allows to extracts data from a specific data set - stored in the work directory - and performs a simple equation. Results are then stored in a new data set.

proc iml  ;
use B1;
read all var _ALL_  into B1;
close B1;

g= B1[1,1];
ExG = B1[3,1];
Res = B1[5,1];
E =2;  
R =3;  

test =  g/(g+(ExG/E)+(Res/(R*E)));

print g ExG res test  [format = 10.6];
create try var {g ExG res test};
append;
close try;

run; quit;

I now would like to apply this procedure to several data sets stored in my SAS work folder and append results combined in new folder. Data sets are coded as B1, B2, B3... I think, a SAS macro with a loop function is needed but my knowledge in this area is very limited. I would appreciate any help.

Is there a reason you are using IML for this? It looks like you are just using three scalar values from each table, so not sure why you would use a matrix language. You can use macro to generate any text you want, so it could easily generated IML code. But I believe that IML itself can be used to generate code instead if you want. — Tom, Nov 30 '17 at 15:00
While the linked duplicate is "data step", the concepts are still broadly applicable to IML. Take a look at that question/answer and then consider asking a new question if you have trouble adapting your issue to that. As far as making a macro in the first place, you'll want to do some research on your own as to how to do that, it's too broad a question here. — Joe, Nov 30 '17 at 21:47
@Tom: Many thanks for the code and the good explanations. It works perfectly. Basically I used IML as for me it was the easiest method to extract data and perform calculations, and also I think it can be extended to more complex tasks in a easy way. — user3570605, Dec 01 '17 at 14:16
@Joe: Thanks for the link to the similar question. I am happy for every advice to improve my skills. I put together a macro using proc SQL with a %let and %do step to extract columns/variables from a single data set and perform a procedure for all columns/variables step by step. However I struggled to apply proc SQL to extract the information from separate data files and then to apply the procedure. I will look into this. — user3570605, Dec 01 '17 at 14:24

Thogerar · Accepted Answer · 2017-11-30T15:19:12.270

Indeed, you can use a macro fonction to do it easily like this :

1) Set an empty data set:

data result;
run;

2) Create a macro fonction to process your dataset. The VAR macro-variable will be the number of dataset from your list B1,B2,B3,B4.

%macro processDatasets(var);

proc iml  ;
use B&var;
read all var _ALL_  into B&var;
close B&var;
g=  B&var.[1,1];
ExG = B&var.[3,1];
Res = B&var.[5,1];
E =2;  
R =3;

You here store the value with a call symputx("g", B&var.[1,1], 'G');. And then add the global macro variable into the dataset to_store.

call symputx("g", B&var.[1,1], 'G');.
....

test =  g/(g+(ExG/E)+(Res/(R*E)));
print g ExG res test  [format = 10.6];
create try var {g ExG res test};
append;
close try;
run; quit;

Here is the way how to store the macro variable into the current dataset.

data to_store;
g=call symget("g");
ExG=call symget("ExG");
...
run;

3) Here is the tips to concatenate multiple dataset into a result one.

data result;
    set result to_store;
run;

%mend processDataset;

4) You can do several call to the function :

%processDataset(1);
%processDataset(2);
%processDataset(3);
...

5) Or you can loop through a list of number :

%macro processVarList;
%let var_list=1|2|3|4;
    %let k=1;
    %do %while (%qscan(&var_list, &k,|) ne );
        %let VAR = %scan(&var_list, &k,|);
        %processDataset(&var);
        %let k = %eval(&k + 1);
    %end;
%mend processVarList;
%processVarList;

6) You can adapt this code following what you want to store in the result dataset. I didn't test the code but the idea is there. I guess you have to store all your variable G, ExG, Res, E, R into a dataset to be merge into the final result dataset.

At the end you get all your value into the result dataset.

How to perform same SAS procedure over multiple datasets

1 Answers1