sas loop over datasets

[joaolopes]

Hi,

I am trying to import from a database the datasets a, b, c, d, e, f

I can do the following to import it

%imp(a, a);
%imp(b, b);
%imp(c, c);
%imp(d, d);
%imp(e, e);
%imp(f, f);

Please can anyone tell me if there is a simple way to create a list and then do a loop over the list?

something like

%let name_list= a b c d e f
%local i next_name;
%do i=1 %to %sysfunc(countw(&name_list));
   %let next_name = %scan(&name_list, &i);
   %imp(&next_name, &next_name);
%end;

This command does not work.

Can anyone help me with this?

Thanks

[Reeza]

Use a data step and CALL EXECUTE or DOSUBL.

See the last step in this example:

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

And another that's a fully worked example:

https://gist.github.com/statgeek/beb97b1c6d4517dde3b2

There's several steps included here primarily to illustrate what's happening. You can simplify this code/reduce the steps.

/********************************************************************
Example : Call macro using parameters from data set
********************************************************************/

*define macro to be called later;
%macro summary(age=, sex=);

proc print data=sashelp.class;
	where age=&age and sex="&sex";
run;

%mend;

*sort data to ensure correct order for next steps;
proc sort data=sashelp.class out=class;
by age sex;
run;

*create macro string to be called in next step. Note the macro is called at the last of each reord;
data sample;
set class;
by age sex;

if last.sex;
string =
    catt('%summary(age=', age, ',sex=', sex, ');');
put string;
run;

*Call macro - can be done in previous step but broken out here for demonstration purposes;
data _null_;
set sample;
call execute(string);
run;

[joaolopes]

I am new to sas, so I don't understand your code template.

[Reeza]

Did you read the comments and instructions? Or run it? You should be able to run it and check each step.

My code should be pretty similar to what you're doing. 

Is there a something specific you don't understand?

---

@joaolopes wrote:

I am new to sas, so I don't understand your code template.

---

[joaolopes]

a b c d e f are datasets that I want to import, not variables

In your code the example is sex and age which seem to be variables, not datasets.

I am probably confused about this

[Astounding · Accepted solution]

For someone who is new to SAS, you have done well to get this far.  Two issues ...

In calling the macro, you would call it with just a single parameter:

%imp(&next_name)

The semicolon is not needed.  Some prefer it as a matter of style.

The major issue is that you can't use %DO just anywhere.  It has to appear within a macro definition.  So first define the macro:

%macro do_all (name_list=);

   %* Your logic goes here ... everything you posted except for the first %LET statement;

%mend do_all;

Then call the macro:

%do_all (name_list=a b c d e f)

[joaolopes]

This worked! Thanks!

My final code is the following

%macro imp(outdset,intable);
	proc sql;
		connect to odbc (dsn='trialdata');
		create table sas_data.&outdset as select * from connection to odbc(select * from &intable);
	quit;
%mend imp;

%macro do_all (name_list=);
%local i next_name;
%do i=1 %to %sysfunc(countw(&name_list));
   %let next_name = %scan(&name_list, &i);
   %imp(&next_name, &next_name);
%end;
%mend do_all;
%do_all (name_list=a b c d e f);

[Reeza]

Step1: Make a data set of all the variables you need to call your macro

Step 2: Make a string that looks like your macro call, %imp(a, a)

Step 3: Use CALL EXECUTE to run the string. 

data list_datasets;
*step 1;
input var1 $ var2 $;

*step 2;
*create a string that looks like your macro call;
str = catt('%imp(', var1, ',', var2, ');');
cards;
a a
b b
c c
d d
e e
f f
;
run;



data run_macro;
set list_datasets;
*step 3;
*will run macro for every str line in previous data set;
call execute(string);
run;

You should run any of the demo's I posted to see how that works fully, but the solution to the question you asked is above. 

Also, why have two parameters that are the same? That seems redundant at best. 

---

@joaolopes wrote:

Hi,

I am trying to import from a database the datasets a, b, c, d, e, f

I can do the following to import it

%imp(a, a);
%imp(b, b);
%imp(c, c);
%imp(d, d);
%imp(e, e);
%imp(f, f);

Please can anyone tell me if there is a simple way to create a list and then do a loop over the list?

something like

%let name_list= a b c d e f
%local i next_name;
%do i=1 %to %sysfunc(countw(&name_list));
   %let next_name = %scan(&name_list, &i);
   %imp(&next_name, &next_name);
%end;

This command does not work.

Can anyone help me with this?

Thanks

---

[joaolopes]

Thanks for your help