CODEWITHSUNDEEP
sqlsas
Josh
Josh

Reputation: 99

Refer to a macrolist which name depends on a variable

%let list_2=‘a’,’b’,’c’;
PROC SQL;  

CREATE TABLE test AS  

SELECT    
     Bin,  /*value is a*/  
     Rank, /*value is 2*/  
     CASE  
     WHEN Bin IN (&list_Rank.)   /*list_2=‘a’,’b’,’c’*/  
     THEN 1  
     ELSE 0  
     END     AS test   

FROM     Source  
;  
QUIT;

I am looking for a way to use the value of column Rank in the reference to the macrovariable list_2.

Upvotes: 0

Views: 64

Answers (2)

Tom
Tom

Reputation: 51621

You could add another CASE statement. Actually since it is SAS code you could eliminate the current CASE statement and just use the result of the IN operation which will be 0 (false) or 1 (true).

%let list_1='a','b','c';
%let list_2='d','e','f';
%let nlists=2;

PROC SQL;  

CREATE TABLE test AS  
SELECT    
     Bin   /*value is a*/  
   , Rank  /*value is 2*/  
   , case
       when (rank=1) then bin in (&list_1)
       when (rank=2) then bin in (&list_2)
       else 0  
     end as test    
FROM Source  
;  
QUIT;

If you are running inside a macro you could use a %do loop to generate the when clauses.

%do i=1 %to &nlists ;
  when (rank=&i) then bin in (&&list_&i)
%end;

If you are not in a macro then create a macro to run the %do loop and call it at the appropriate place.

Upvotes: 3

Quentin
Quentin

Reputation: 6378

As @John commented, you can use SYMGET function to use data to look up the value of a macro variable during run-time. I don't think you can use the IN operator for what you want, because it expects a list of character strings, while SYMGET will return a single string. Below I took the quotes out of the &LIST_2 (just to make it easier to work with), and used FINDW to do the work of IN. I think this is along the lines of what you're describing:

%let list_2=a,b,c;
%let list_3=d,e,f ;

data have ;
  input bin $1. rank ;
  cards ;
a 2
a 3
e 2
e 3
;
run ;

proc sql ;
  select
    bin
   ,rank
   ,case when findw(symget(cats("list_",rank))
                   ,bin
                   ) then 1
    else 0
    end as test
   ,symgetc(cats("list_",rank)) as symgetc /*debugging*/
   from have ;
quit ;

Upvotes: 4

Related Questions