Macrovariable for list of values

[ravimegharaj]

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.

[rogerjdeangelis]

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;

[Jagadishkatam · Accepted solution]

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;

[ravimegharaj]

Hi Jag,

Thanks for the code.

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

Thanks,

[rogerjdeangelis]

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