topic Create a macro variable for each unique value of a variable. in SAS Programming

Create a macro variable for each unique value of a variable.

rnmishra — Fri, 28 Dec 2012 18:58:55 GMT

The values of a variable -var1 of my data set changes every month.

I am interested to create a macro variable for each unique value of the variable -Var1.....This time I have 5 different (unique) values of Var1. The macro variable &NumUni counts the number of unique values of Var1.

Here is my attempt!

The data set have contains a row for each unique value of Var1.

%MACRO LOOP;
          %DO I=1 %TO %sysevalf(&NumUni);
              DATA _NULL_;
               SET Have;
               IF _N_=&I THEN CALL SYMPUTX('X&I',Var1);
               RUN;

%END;
%MEND LOOP;

In this case NumUni=5, so I should be creating 5 macro variables X1, X2,X3,X4 and X5.

There is some thing wrong with SYMPUTX('X&I',Var1);

Any help would be highly appreciated!

Thanks!

Re: Create a macro variable for each unique value of a variable.

shivas — Fri, 28 Dec 2012 19:41:11 GMT

Hi,

Try this..you can also use count(name) into a macro variable.

%let NumUni=10;

proc sql;

select distinct name into :var1-:var&NumUni from sashelp.class;

quit;

Thanks,

Shiva

Re: Create a macro variable for each unique value of a variable.

rnmishra — Fri, 28 Dec 2012 19:47:15 GMT

Thanks.

Re: Create a macro variable for each unique value of a variable.

Reeza — Fri, 28 Dec 2012 20:17:43 GMT

Your code seems correct, except macro variables only resolve inside double quotes not single quotes.

You also don't need macro code, here's a datastep solution that assumes you have a sorted dataset and there are variations to handle unsorted data but you don't have to know the number of records ahead of time.

data _null_;

set sashelp.class;

by name;

count=0;

if first.name then count+1;

call symput("x"||compress(count), name);

run;

%put &x1;

%put &x12;

Re: Create a macro variable for each unique value of a variable.

rnmishra — Sat, 29 Dec 2012 17:22:00 GMT

Thanks all of you for your help.

For any beginner like this one, the following material provides some extra tips.

SAS(R) 9.3 SQL Procedure User's Guide