Macrovariable for list of values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Macrovariable for list of values

Hello Experts,

 

I want to call a macro variable in case statement in proc sql with list of values.

I have a table with all the list of values with column name - Empcode. I want to create a macro variable &empcd. which holds all the values within Empcode field ie "EM12","EM34" etc.

This then will be used in proc sql case statement ie

proc sql;

create table emp_list as (

select a.*,

case when a.emp_id in (&empcd.) then "Match" end as analysis

from employee a

);

quit;

 

I am thinking this as an alternative approach, rather than using 2 tables with left join to achieve the same output.

 

Many thanks.


Accepted Solutions
Solution
‎03-21-2017 09:37 PM
Super User
Posts: 1,115

Re: Macrovariable for list of values

ok, consider that you have a table with employee id's then create the macro variable with list of employee id's in quotes and separated by the comma

 

proc sql;
select distinct quote(Empcode) into: empcd separated by ',' from employeeids;
quit;

proc sql;
create table emp_list as select a.*,
case when a.emp_id in (&empcd.) then "Match" else " " end as analysis
from employee a;
quit;

 

 

 

Thanks,
Jag

View solution in original post


All Replies
Valued Guide
Posts: 505

Re: Macrovariable for list of values

HAVE (list of names in meta dataset and sashelp.class)
=======================================================

Up to 40 obs WORK.NAMES total obs=7

Obs    NAME

 1     James
 2     Jane
 3     Janet
 4     Jeffrey
 5     John
 6     Joyce
 7     Judy

SASHELP.CLASS
Up to 40 obs from sashelp.class total obs=19

Obs    NAME       SEX    AGE    HEIGHT    WEIGHT

  1    Alfred      M      14     69.0      112.5
  2    Alice       F      13     56.5       84.0
  3    Barbara     F      13     65.3       98.0
  4    Carol       F      14     62.8      102.5
  5    Henry       M      14     63.5      102.5
  6    James       M      12     57.3       83.0
...
 16    Robert      M      12     64.8      128.0
 17    Ronald      M      15     67.0      133.0
 18    Thomas      M      11     57.5       85.0
 19    William     M      15     66.5      112.0

WANT
====

NAME      SEX       AGE    HEIGHT    WEIGHT
-------------------------------------------
James     M          12      57.3        83
Jane      F          12      59.8      84.5
Janet     F          15      62.5     112.5
Jeffrey   M          13      62.5        84
John      M          12        59      99.5
Joyce     F          11      51.3      50.5
Judy      F          14      64.3        90

SOLUTION
========

* create the list of names;
data names;
 set sashelp.class(keep=name where=(name=: 'J'));
run;quit;

proc sql;
  select
     quote(name)
  into
    :names separated by ','
  from
     work.names;
  select
    *
  from
    sashelp.class
  where
    name in (&names);
;quit;

Solution
‎03-21-2017 09:37 PM
Super User
Posts: 1,115

Re: Macrovariable for list of values

ok, consider that you have a table with employee id's then create the macro variable with list of employee id's in quotes and separated by the comma

 

proc sql;
select distinct quote(Empcode) into: empcd separated by ',' from employeeids;
quit;

proc sql;
create table emp_list as select a.*,
case when a.emp_id in (&empcd.) then "Match" else " " end as analysis
from employee a;
quit;

 

 

 

Thanks,
Jag
Occasional Contributor
Posts: 6

Re: Macrovariable for list of values

Hi Jag,

 

Thanks for the code.

 

I'm getting white spaces between value and quote from first code ie "EMP12 ","EMP34 ", etc

 

Thanks,

Valued Guide
Posts: 505

Re: Macrovariable for list of values

It does not matter, however if you want to eliminate the space trim(name) into.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 130 views
  • 3 likes
  • 3 in conversation