BookmarkSubscribeRSS Feed
Calcite | Level 5

Hi all,


I have a set of about 300 different macro variables each storing a list of product codes and I also have a dataset, listing all used product codes and their counts etc.. The codes in the 300 macro variables are only a part of all possible product codes and group the product codes into bigger categories.


Is there a way to access the product code information stored in the macro variables to create a new variable in my dataset, which takes certain values depending on the specific codes stored in the different macro variables? I tried the following code but this does not work. Does anyone have an idea how I can do it?


data b; set a ;
%macro groupcodes;
%do j=1 %to 300; %let e=%scan(&varlist3,&j); %if Bcode in (&&x&e) %then druggroup="&e";
%end; %mend; %groupcodes


What I  would like to have in the end is a dataset  which looks like the exmple below:

bcode druggroup

b123     a

b234     b

b122     a

b129     a

b255     c

b567     g

...          ...


Thank you very much in advance.

Diamond | Level 26 RW9
Diamond | Level 26

Please picture someone banging their head against the wall.  Macro is not the place to be storing or processing data.  Data is stored in datasets - it even states this in the name.  You process data using Base SAS - this is the programming language.  Macro is nothing more than a text find/replace system, it does nothing itself but generate Base SAS code.  Macro is an advanced topic for very specific scenarios, almost all coding effort should be put into using Base SAS to do your tasks.

Step 1, create a dataset which contain all the codes with a grouping variable, e.g.


a              b123

a              b122


b              b234



Now you have a nice list of codes and groups which is easy to manipulate and use in other code.

Step 2, merge the codes group onto your data based on code=code:

proc sql;
  create table want as 
  select  a.*,

  from    have a
  left join codes b
  on      a.code=b.code;

Voila.  Easy, simple, fast code to achieve what you need.  Simply put, use Base SAS programming always, and only if there is some benefit, then add in some macro code.

Super User

Second what @RW9 said. You are abusing the macro language for something it is not meant for.

See Maxims 11, 14, 8, 7.


Go back to where you are defining those 300 macro variables, and store the values in a dataset. Or use the existing dataset, if you already have one.

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
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
  • 2 replies
  • 3 in conversation