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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.