BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ravimegharaj
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

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

4 REPLIES 4
rogerjdeangelis
Barite | Level 11
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
Amethyst | Level 16

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
ravimegharaj
Fluorite | Level 6

Hi Jag,

 

Thanks for the code.

 

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

 

Thanks,

rogerjdeangelis
Barite | Level 11

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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