Passing macro values to run the subsequent steps repeatedly

[David_Billa]

I would like to know how to pass macro values to run certain steps repeatedly. For example the macro input may resolve to anything as below.

&Input ->  table1

&input ->  table1 table2

&input ->  table1 table2 table3

Then I want to run the following steps for each macro value. If the macro variable has 'table1' as value then I don't have a problem. If it has more than one values as I mentioned above then I'm not certain how to make the below step to run for each value. 

Data output;
Set &input.;  /* I want to execute this data step for each macro value and I don't want to append the dataset in single shot by giving both or all macro values in set statement */
run; 

[Kurt_Bremser]

Use a macro do loop:

%macro run_the_loop;
%do i = 1 %to %sysfunc(countw(&input.));
data output;
set %scan(&input.,&i.);
run;
%end;
%mend;
%run_the_loop

[David_Billa]

Whether I have to make any change around coundw function if the macro
variable value resolves to table_1, table_2, table_3......?

[Tom]

---

@David_Billa wrote:
Whether I have to make any change around coundw function if the macro
variable value resolves to table_1, table_2, table_3......?

---

If you want to use a different delimiter than you need to tell both the COUNTW() and %SCAN() functions to use that delimiter.

Note comma is NOT a good choice to use as the delimiter because it will be hard to pass that as the value of a macro parameter since comma is used as the delimiter between parameters in a macro call.

%let list=A|B|C;
%do i=1 %to %sysfunc(countw(&list,|));
  %let next=%scan(&list,&i,|);
...

[Tom]

Normally I would use a space delimited list as the value of the macro variable. For some SAS code you want to generate you can just pass that value onto SAS. For example if it is a list of variables that you want to produce frequencies on.

%let varlist=age sex;
....
proc freq data=sashelp.class;
  tables &varlist;
run;

Otherwise add a %DO loop to parse the values one by one and use them.

%let dslist=table1 table2 table2 ;
%do i=1 %to %sysfunc(countw(&dslist,%str( ));
  %let dsname=%scan(&dslist,&i,%str( ));
....
proc glm data=&dsname ;
....
%end;

To make such code you will need to do it as part of macro definition.

[ballardw]

This question shows a certain unfamiliarity with base SAS code.

You can have multiple data set names on a SET statement. That is one way to append data sets together. The requirement syntactically is only that all of the sets exist.

So

data want;
   set one two three  somelib.thisname thatlib.difset;
run;

is just fine as long as all 5 sets exist in the current session.

Caution:

Any variables of the same name but different type in different sets, such as "id" in set one is character and "id" in set two is numeric, will be an ERROR.

Example:

data work.one;
   x=123;
run;

data work.two;
   x='123';
run;

data work.want;
   set work.one work.two;
run;

17   data work.want;
18      set work.one work.two;
ERROR: Variable x has been defined as both character and numeric.
19   run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.WANT may be incomplete.  When this step was stopped there were 0
         observations and 1 variables.

If the sets are in other than the Work library you will need to make sure that you have the libname with each set name.

[Quentin]

I'm a little confused.  If the macro variable &input passed the value table1 table2 table3, what is the code you want to generate?

Do you want:

data output;
  set table1 table 2 table3 ;
run; 

Or do you want:

data output1 ;
  set table1 ;
run; 
data output2 ;
  set table2 ;
run; 
data output3 ;
  set table3 ;
run; 

Or something else?  

Please post your current version of the macro.

[David_Billa]

No possibilities to get the data type error or any attributes kind of error as you mentioned as all the fields in the input tables are compatible with the final output dataset.

[David_Billa]

I want to create one common output table by combining all the other input datasets.

So I need the code in the fashion as below,

Data output;
Set table1;
run;

Data output;
set table2;
run;

[Kurt_Bremser]

---

@David_Billa wrote:
I want to create one common output table by combining all the other input datasets.

So I need the code in the fashion as below,

Data output;
Set table1;
run;

Data output;
set table2;
run;

---

This does not combine anything, only the data from the last table will end up in output.

To combine the tables, do

data output;
set
  table1
  table2
;
run;

[David_Billa]

I would like to know how to accomplish that using macro values in a set
statement.

[Kurt_Bremser]

---

@David_Billa wrote:
I would like to know how to accomplish that using macro values in a set
statement.

---

%let input = table1 table2 table3;

data output;
set &input.;
run;

That's all.

[Quentin]

---

@David_Billa wrote:
I want to create one common output table by combining all the other input datasets.

So I need the code in the fashion as below,

Data output;
Set table1;
run;

Data output;
set table2;
run;

---

That will not create one table with the combined records from the datasets. It will create work.output once with the records from table1, then will overwrite work.output with the records from table2. 

The code:

data output;
  set table1 table 2 table3 ;
run; 

will concatenate the three tables Table1-Table3 and write work.output with all the records.

For that, your original code will work:

Data output;
Set &input.;  /* I want to execute this data step for each macro value and I don't want to append the dataset in single shot by giving both or all macro values in set statement */
run; 

The value of &input could be Table1 or Table1 Table2 Table3 and it will work.

[David_Billa]

May I know why you have used %str() function here?

[Tom]

---

@David_Billa wrote:
May I know why you have used %str() function here?

---

You use the %STR() to add macro quoting around text so that it is handled differently. For this problem if the delimiter is either a space or a comma you would need the macro quoting so that the space or comma is treated as the value to use as the delimiter.  Without the quoting spaces are ignored and commas are treated as delimiters for the arguments to the function calls instead of the value of the argument.