BookmarkSubscribeRSS Feed
marleeakerson
Calcite | Level 5

I am trying to edit a table down by selecting only the observations with certain entries. I want a table where the only observations are the ones where the variable CODE is equal to 59400, 59410, 59510, 59515, or 59610 .

 

Here is the code: 

 

 

DATA want;
SET have;
if CODE = 
59400 59410 59510 59515 59610
then keep ;
run;

 

But for some reason I keep getting a table with 0 observations. Does anyone know why this is?

 

2 REPLIES 2
Reeza
Super User
Assuming the codes are numeric you're looking for IN

ie if code in (59400 59410 59510 ... ) then keep;

if the variables are character the values in the parentheses need to have quotes:

if code in('59400' '59410' ... );
unison
Lapis Lazuli | Level 10

There are several ways to do this in SAS. I've listed a few in my code below. My personal favorite is to remove my 'code' list into a custom format (option 3 below) that way it is in one spot in case I ever need to update it.

 

/*Assuming code is numeric, otherwise encapsulate each code in '' (i.e. '59400' vs. 59400)*/
data have;
	input code;
	datalines;
1
59515
59400
5
59410
59510
;
run;

data desired_output;
	input code;
	datalines;
59515
59400
59410
59510
;
run;

/*Option 1*/
data opt1;
	set have;

	if code in (59400 59410 59510 59515); /*equivalent to: if code in (59400 59410 59510 59515) then output; */
run;

/*Option 2: place in set (where=) -- only reads in observations that match your criteria*/
data opt2;
	set have(where=(code in (59400 59410 59510 59515)));
run;

/*Option 3: remove logic from data step and place in custom format*/
proc format;
	value mycodes
		59400, 59410, 59510, 59515="1"
		other="0"
	;
run;

data opt3a;
	set have;

	if put(code,mycodes.); /*equivalent to: if put(code,mycodes.)="1" then output; */
run;

data opt3b;
	set have(where=(put(code,mycodes.)="1"));
run;

/*Option 4: remove logic from data step and place in macro variable*/
%let mycodes = (59400 59410 59510 59515);

data opt4a;
	set have;

	if code in &mycodes; /*equivalent to: if put(code,mycodes.)=1 then output; */
run;

data opt4b;
	set have(where=(code in &mycodes));
run;

/*Checking that results=desired_results*/
proc compare base=desired_output compare=opt1;
proc compare base=desired_output compare=opt2;
proc compare base=desired_output compare=opt3a;
proc compare base=desired_output compare=opt3b;
proc compare base=desired_output compare=opt4a;
proc compare base=desired_output compare=opt4b;
run;

 

-unison

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 751 views
  • 1 like
  • 3 in conversation