Creating multiple macro variables from values of one variable

[TXSASneophyte]

Howdy!

I have a dataset with a variable called "CLASS" (character variable) that contains 23 different values in it. Another variable called "CD" (character variable) has unique codes in it associated with each value in "CLASS". I want to create macro variables for each value found in "CLASS" containing the associated codes from "CD". In other words, I need to create 23 macro variables with their related codes.

Normally, I would do PROC SQL :INTO for this, but I do not know how to customize it so that it efficiently creates all the macro variables I need at once with their corresponding codes.  

I am using SAS 9.4

Let me know if things need to be made clearer!

[RW9]

Well, firstly, don't.  Macro isn't the place to be manipulating data, that is what Base SAS is for.  Macro just generates text.

If you show why you want to do this, I can simplfy for you.

As for your question, you can merge the two and do select into as normal:

proc sql;

  select  catx('-',A.VAR1,B.VAR2) 

  into     :MVAR

  from    TABLE1 A  

  full join TABLE2 B

  on...

But really, there are far bettter ways of programming to avoid all the above.

[Astounding]

This task is much better suited to a DATA step than SQL:

data _null_;

set have;

call symputx(class, cd);

run;

This assumes that the values for CLASS are legitimate SAS names.  It uses the values of CLASS as the names of the macro variables.

[TXSASneophyte]

This is great! Is there a way to do this procedure but only make macro variables for the unique values? The current code made duplicate macro variables because there are duplicate values in "CLASS", but there are only 23 unique values. I ask just to learn and make this a tidier! My initial thought is to create a separate dataset with only the unique values and then running your code.  

[Shmuel]

You can use SQL to select distinct class, CD and

run @Astounding code on the result of it.

Thus assuming that CLASS is character type.

[Astounding]

Yes, creating a separate data set first would work.

There may be ways to create the macro variables in the same step that performs the subsetting.  Here are a couple of ideas that may or may not apply, depending on how your data is structured:

data _null_;

set have (obs=23);

call symputx(class, cd);

run;

data _null_;

set have;

by class;

if first.class then call symputx(class, cd);

run;

[TXSASneophyte]

Perfect, thank you!