DATA Step, Macro, Functions and more

%DO LOOP macro

Reply
N/A
Posts: 0

%DO LOOP macro

I must preface my question by saying I’m a beginner level SAS user and have not worked with macros much. I’ve read up on some materials relating to macros but I can’t find specific examples related to what I’m working on. I need to run several proc sql statements and data steps based on a specific category name within a list. I’ve written my code to create a macro where I have to enter the specific category then run the code. Is there a way to run the code automatically for the entire list of categories and then create separate outputs for them without having to type in the category each time? I’ve seen some info about %DO loops in a macro statement but I’m not sure if this is what I should use. I’ve included a sample of the code I’ve written with a basic macro. Is there a way to do this?


%let catg = 'BEVERAGES';
%let catgdesc = "Beverages";
%SYSLPUT catg = &catg;
%SYSLPUT catgdesc = &catgdesc;

rsubmit;
proc sql;
create table skuinfo1 as
select sku, major, minor, category, sub_category
from skuinfo
order by 6;
quit;
endrsubmit;

rsubmit;
data skuinfo2;
set skuinfo1;
category = category;
if category in (&catg) then do; major = 999; end;
if major = 999 then do; category = &catgdesc; end;
run;
proc print data = skuinfo2 (obs=100);
where major = 999;
run;
endrsubmit;

rsubmit;
proc sql;
create table skus as
select *
from skuinfo2
where category in (&catgdesc.);
quit;
proc print data = skus (obs=2);
run;
endrsubmit;
SAS Employee
Posts: 58

Re: %DO LOOP macro

Maybe you could do something like this:

%do i=1 %to 5; /*or however many categories there are */
%if &i=1 %then
%do;
%let catg = 'BEVERAGES';
%let catgdesc = "Beverages";
%end;
%else %if &i=2 %then
%do;
%let catg = 'FOODS';
%let catgdesc = "Foods";
%end;

/* etc etc until you have blocks for all categories */

/* Then after the if / else if / else block, put all of your other code here */

%end;
Super Contributor
Super Contributor
Posts: 365

Re: %DO LOOP macro

Hello 5557691,

I've found a standard approach to the problems of this kind (code not tested, sample is necessary):
[pre]
/* Categories and Descriptions into macro variables */;
proc SQL noprint;
select distinct category into :n from skuinfo1;
%let n=%trim(&n);
select category into :c1-:c&n from skuinfo1;
select catgdesc into :d1-:d&n from skuinfo1;
quit;
/* Loop on caterories and descriptions */;
%macro a;
%local i catg catgdesc;
%do i=1 %to &n;
%let catg=&&c&i;
%let catgdesc=&&d&i;

%end;
%mend a;
%a
[/pre]
Sincerely,
SPR
Valued Guide
Posts: 632

Re: %DO LOOP macro

Ask a Question
Discussion stats
  • 3 replies
  • 137 views
  • 0 likes
  • 4 in conversation