BookmarkSubscribeRSS Feed
TXSASneophyte
Obsidian | Level 7

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!

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
PROC Star

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
Obsidian | Level 7

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
Garnet | Level 18

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
PROC Star

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;

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
  • 6 replies
  • 3987 views
  • 5 likes
  • 4 in conversation