access information stored in macro variable to create new variable

[Li17]

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 ;
druggroup='M';
%macro groupcodes;
%do j=1 %to 300; %let e=%scan(&varlist3,&j); %if Bcode in (&&x&e) %then druggroup="&e";
run;
%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.

[RW9]

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.

GROUP   CODE

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.*,
          b.group
  from    have a
  left join codes b
  on      a.code=b.code;
quit;

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.

[Kurt_Bremser]

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.

https://www.youtube.com/watch?v=cRl-dmeLTT8