Make macro variable for values of a variable

[mandan414]

Hello, 

I'm quite new in Macro and would appreciate if somebody helps me with this. 

I have a variable (myvariable) with the value of A10 to A30. I was wondering if it is possible to make a macro variable for these values (A&i) then I don't have to type the A10 .....A30  in my codes. 

For example I need to say 

data new;

    set old; 

         if myvariable in ('A10' , 'A11' , 'A12' , 'A13' , 'A14' ) then myvariable = 0;

         else if myvariable in ('A16' , 'A17' , 'A18' , 'A19' , 'A20' ) then myvariable = 1;

.........

run; 

I would like to avoid typing the 'A10' , 'A11' , 'A12' , 'A13' , 'A14'.... 

Thank you, 

Mandana 

[novinosrin]

One way is to use, 

User defined formats i.e PROC FORMAT invalue and store the format in as permanent INformats in a library. You can reuse as many times as you want.

Another way is, 

Store in a Dataset table, and you can load the dataset into memory table(HASH table) and do the look up.

The above two will only require a one time effort.

And  lastly 

Store in a macro variable at the top, and call that macro variable everywhere 

[mandan414]

I'm not quite sure I understand your solution. I need to do this:
if myvar1 in ('A10', 'A11', 'A12', 'A13', 'A14') then myvar1=1;
else myvar1=0;
If myvar2 in ('A20', 'A21', 'A22', 'A23', 'A24', 'A25') then myvar2=1;
Else myvar2=0;
.......

[novinosrin]

What I meant is

proc format;
 invalue recode (upcase just)
 'A10' , 'A11' , 'A12' , 'A13' , 'A14'=0
 'A16' , 'A17' , 'A18' , 'A19' , 'A20'=1;
 run;

 data have;
 input var : $quote5.;
 cards;
 'A10'
 'A11' 
 'A12'
 'A13' 
 'A14' 
 'A16'
 'A17'
 'A18'
 'A19'
 'A20'
 ;
 

 data want;
 set have;
 new_var=input(var,recode3.);
 run;

You can reuse the informat downstream as many times as you want.

[Vish33]

I agree with @novinosrin  to use proc format to store the values and use it any time you want . when it comes to pull the data in dynamic way i usually prefer to store them in macro variables , so i can pull the data dynamically irrespective of the number of observations and the data in it. 

proc sql;
	select count(1) into :cnt from have;
	run;
	%let cnt= %sysfunc(compress(&cnt.));
	%let i=1;

Proc sql;
	select myvariable into :a&i. - :a&cnt. from have;
quit;

%put a1= &a1. a2= &a2.  a3=&a3. a4=&a4.   ; #and so on ..

[Tom]

That is quite a Rube Goldberg method of creating a series of macro variables using PROC SQL.  Just tell SAS to make the varibles. If you need the count for some purpose then store the value of the automatic macro variable SQLOBS.

proc sql noprint
 select myvariable into :a1- from have;
%let cnt=&sqlobs;
quit;

---

@Vish33 wrote:

I agree with @novinosrin  to use proc format to store the values and use it any time you want . when it comes to pull the data in dynamic way i usually prefer to store them in macro variables , so i can pull the data dynamically irrespective of the number of observations and the data in it. 

proc sql;
	select count(1) into :cnt from have;
	run;
	%let cnt= %sysfunc(compress(&cnt.));
	%let i=1;

Proc sql;
	select myvariable into :a&i. - :a&cnt. from have;
quit;

%put a1= &a1. a2= &a2.  a3=&a3. a4=&a4.   ; #and so on ..

---

[ballardw]

The lists are easy:

%let list1 = 'A10' , 'A11' , 'A12' , 'A13' , 'A14' ;
%let list2 = 'A16' , 'A17' , 'A18' , 'A19' , 'A20' ;

However your code has a problem with :

 if myvariable in ('A10' , 'A11' , 'A12' , 'A13' , 'A14' ) then myvariable = 0;
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^                    ^
                           Character values                             Numeric value for character variable
         if myvariable in ('A16' , 'A17' , 'A18' , 'A19' , 'A20' ) then myvariable = 1;

You would use the macro variable like 

data new;
   set old;
   if myvariable in (&list1.) then <something>;

I would recommend that perhaps you should not actually put the commas in the list though and ensure you have a single space between values.

[Jagadishkatam]

Alternatively please try the perl regular expressions as below

data have;
input text$;
if prxmatch('/[A][1][0-4]/',compress(text)) then flag=0;
else if prxmatch('/[A][1-2][0-9]/',compress(text)) then flag=1;
cards;
A10
A11
A12
A13
A14
A15
A16
A17
A18
A19
A20
;

[hashman]

@mandan414:

1. First, you have a data type conflict, as it follows from your code that either (a) myvariable is numeric but you're comparing it to character values or (b) it is character but you want to assign a numeric value 0/1 to it. Either way, you'll get a bunch of type conversion notes in the log. To avoid them, in case (a) convert it to $ on input as put(myvariable,3.) and use this expression instead of myvariable in the IF clause; or in case (b) assign "0"/"1" instead of 0/1. Better yet, use a different, new, variable to create your Boolean flag.

2. Second, if you just don't like tedious hard coding, you can shorten your list notation like so:   

if "A10" <= myvariable <= "A14"

3. Third, it's still hard coding, albeit less tedious. Not sure why you mention "macro" in the context but let's assume that you mean using macro variables to parameterize your code in order to avoid hard coding altogether. The latter is a good idea; but using macro variables for the purpose is, generally speaking, not, especially since all you need is a set of lookup values. It's much better and more robust and scalable to store them in a file external to the program and make the program read it and create a lookup table of your choice.

@novinosrin showed you one way to do it using an informat (considering point #1, you may actually need a format, depending on the actual data type of myvariabe). However, if you simply store the lookup values in a SAS data set (which you can edit without touching the code) and make the program read it to create an in/format by composing a CNTLIN= data set and feeding it into proc FORMAT; then you can use the resulting in/format just like @novinosrin showed. 

However, the advantages of such an approach don't end here because by having the control data set with the lookup values you can use it with any lookup method you may fancy, for example:

• use it as one side in an SQL join
• index it and use the index as a lookup facility via SET ... KEY=
• load a hash table in the DATA step where you need to do the lookup
• load a hash table into a function using proc FCMP and then use the function to do the lookup in any subsequent step, including in the WHERE clause
• etc.

And of course you can use the control table to populate a bunch of macro variables, if need be, via SYMPUTX in the DATA step or the INTO clause via proc SQL. It avoids hard coding, too, because in your lookup code you'll have only macro variable references rather than their values; but given all the possibilities listed above, I'd wonder why one would opt for such a cumbersome route.     

Kind regards

Paul D.