DATA Step, Macro, Functions and more

Looping macro variables to create table

Accepted Solution Solved
Reply
Contributor
Posts: 39
Accepted Solution

Looping macro variables to create table

[ Edited ]
%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??


Accepted Solutions
Solution
‎09-01-2016 12:54 PM
Super User
Super User
Posts: 7,081

Re: Looping macro variables to create table

[ Edited ]
Posted in reply to camfarrell25

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;

 

View solution in original post


All Replies
Super User
Posts: 19,878

Re: Looping macro variables to create table

Posted in reply to camfarrell25

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. 

Super User
Posts: 5,518

Re: Looping macro variables to create table

[ Edited ]
Posted in reply to camfarrell25

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.

Super User
Posts: 11,343

Re: Looping macro variables to create table

Posted in reply to camfarrell25

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.

Solution
‎09-01-2016 12:54 PM
Super User
Super User
Posts: 7,081

Re: Looping macro variables to create table

[ Edited ]
Posted in reply to camfarrell25

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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1392 views
  • 1 like
  • 5 in conversation