BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
camfarrell25
Quartz | Level 8
%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??

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

4 REPLIES 4
Reeza
Super User

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. 

Astounding
PROC Star

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.

ballardw
Super User

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.

Tom
Super User Tom
Super User

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;

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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