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!
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.
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.
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.
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.
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;
Perfect, thank you!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.