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.
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;
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;
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;
Hi Jag,
Thanks for the code.
I'm getting white spaces between value and quote from first code ie "EMP12 ","EMP34 ", etc
Thanks,
It does not matter, however if you want to eliminate the space trim(name) into.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.