BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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;
3 REPLIES 3
PatrickG
SAS Employee
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;
SPR
Quartz | Level 8 SPR
Quartz | Level 8
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
ArtC
Rhodochrosite | Level 12

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
  • 3 replies
  • 716 views
  • 0 likes
  • 4 in conversation