topic Re: Looping macro variables to create table in SAS Programming

Looping macro variables to create table

camfarrell25 — Thu, 01 Sep 2016 14:31:49 GMT

%macro loop;
%do i=1 %to 15
%let var&i= 'parent_level&i_vender_cde';
proc sql;
create table vendortree as
select 
&var&i
FROM vendorlist;
QUit; 
%end;
%mend loop;
%loop

Fairly new to this macro and loop business and I'm looking for a probably very simple and easy solution which I have not been able to identify.

Basically, my end result is to create a table where the variable included includes all variables from:

parent_level1_vendor_cde to parent_level15_vendor_cde.

Any ideas??

Re: Looping macro variables to create table

Reeza — Thu, 01 Sep 2016 14:33:41 GMT

Take a look at the macro examples, one should help you get started.

Right now it's not clear what you're trying to do...the table vendortree will be replaces/overwritten in each iteration.

Re: Looping macro variables to create table

Astounding — Thu, 01 Sep 2016 14:52:56 GMT

There's much to learn, and you did not pick the simplest example to start. You will need macro language to loop through, each time generating a single field name within the SELECT statement, and possibly adding a comma.

%macro loop;

%local i;

proc sql;

create table vendortree as

select

%do i=1 %to 15;

parent_level&i._vender_cde

%if &i < 15 %then ,;

%end;

from vendorlist;

quit;

%mend loop;

%loop

For now, note that you are permitted to delimit the name of a macro variable with a dot so that SAS knows how many characters are actually part of the macro variable name: &i. vs. &i both refer to the same macro variable.

Re: Looping macro variables to create table

ballardw — Thu, 01 Sep 2016 15:01:23 GMT

First always start with code that does what you want without any macro values at all. Suppose to select 2 variables you have this:

proc sql;
   create table vendortree as
   select 
     parent_level1_vender_cde,
     parent_level2_vender_cde
   FROM vendorlist;
QUit;

Then if you have a Macro variable such as:

%let varlist = parent_level1_vender_cde, parent_level2_vender_cde;

that resolves to just the variables (and note the presence of the comma as required for SQL syntax)

then the above sql call could be:

proc sql;
   create table vendortree as
   select 
      &varlist
   FROM vendorlist;
QUit;

We are not using any quotes because we want the Name of the variable not the value.

So bit you need is how to build that list AND account for the comma

The code below uses the loop information separately to make the variable list and then use it in the SQL code. I have added a parameter to allow you to specify the number of variables of the name to select. The %sysfunc is a macro function that allows you to call datastep functions. The CATX function will place a string indicated at the start of the function call between resolved strings removing blanks at beginning and end. The %quote is to get the quoted value of the comma as the macro language is sometimes picky about quoted strings with ','.

%macro loop(MaxN=);
%do i = 1 %to &Maxn;
   %if &i=1 %then %let varlist= parent_level&i._vender_cde;
   %else %let varlist= %sysfunc(catx(%quote(,), &varlist , parent_level&i._vender_cde));
%end;

proc sql;
   create table vendortree as
   select 
   &varlist
   FROM vendorlist;
QUit; 
%end;
%mend loop;

Call using

%loop(MaxN=15); but you could use integers from 1 to the maximum number of those variables you want.

Re: Looping macro variables to create table

Tom — Thu, 01 Sep 2016 16:50:50 GMT

In general you use macro logic to generate code. So you need to place your %DO loop such that it will generate the variable names as part of a single SELECT statement.

Note that you do not place quotes around macro variable values, unless you want the quotes to be part of the value.

Also note that you need to tell SAS where the macro variable reference ends and constant text begins by adding a period after the macro variable name when building the name of your variable that you want SQL to select from the value of your macro variable.

%macro loop;
%local i sep ;

proc sql;
  create table vendortree as
    select 
%do i=1 %to 15;
      &sep parent_level&i._vender_cde
 %let sep=,;
%end;
    from vendorlist
  ;
quit; 
%end;
%mend loop;
%loop;