%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??
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;
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.
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.
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.
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: