First of all, you talk of SQL, and IF is not a valid keyword in SQL language. To subset observations in SQL, you need to use WHERE:
data have;
input codeid $4.;
datalines;
a111
a112
a113
a114
;
proc sql;
create table want1 as
select *
from have
where codeid = "a111" or "a112" or "a113"
;
quit;
But then look at the log:
78 proc sql;
79 create table want1 as
80 select *
81 from have
82 where codeid = "a111" or "a112" or "a113"
83 ;
NOTE: Table WORK.WANT1 created, with 4 rows and 1 columns.
No observation was filtered out, although "a114" is not in your condition.
The reason becomes clear when WHERE is used in a DATA step:
data want2;
set have;
where codeid = "a111" or "a112" or "a113";
run;
because now the log tells you this:
69 data want2;
70 set have;
71 where codeid = "a111" or "a112" or "a113";
72 run;
NOTE: There were 4 observations read from the data set WORK.HAVE.
WHERE 1 /* eine offensichtlich WAHRE Where-Bedingung (TRUE) */ ;
NOTE: The data set WORK.WANT2 has 4 observations and 1 variables.
There's an obvious TRUE condition in your WHERE.
Why? The condition is equivalent to this:
where (codeid = "a111") or ("a112") or ("a113");
OR separates several conditions to form a compound condition, so the second and third value become conditions on their own. By definition, any non-missing character value evaluates to TRUE.
While WHERE in a DATA step is handed off to the dataset engine, which accepts SQL syntax, the IF is compiled by the data step compiler as data step code, and there conditions must be SAS Boolean values (numeric, zero or missing is FALSE, everything else is TRUE).
data want3;
set have;
if codeid = "a111" or "a112" or "a113";
run;
Log:
69 data want3;
70 set have;
71 if codeid = "a111" or "a112" or "a113";
72 run;
NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
71:23 71:33
NOTE: Invalid numeric data, 'a112' , at Zeile 71 Spalte 23.
NOTE: Invalid numeric data, 'a113' , at Zeile 71 Spalte 33.
codeid=a112 _ERROR_=1 _N_=2
NOTE: Invalid numeric data, 'a112' , at Zeile 71 Spalte 23.
NOTE: Invalid numeric data, 'a113' , at Zeile 71 Spalte 33.
codeid=a113 _ERROR_=1 _N_=3
NOTE: Invalid numeric data, 'a112' , at Zeile 71 Spalte 23.
NOTE: Invalid numeric data, 'a113' , at Zeile 71 Spalte 33.
codeid=a114 _ERROR_=1 _N_=4
NOTE: There were 4 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT3 has 1 observations and 1 variables.
The data step compiler compiles an implicit conversion of character to numeric, but at runtime invalid numeric values are encountered, so the conversion results in missing (FALSE) values, and only when the first part of the condition is met, the whole condition is TRUE.
So this is the reason why you have to use the IN operator and a list of values in parentheses for your code to work as intended.
Now, anytime you need to work with lists, it is a good idea to store them in their own dataset and use that to make your selection, either by JOINing/MERGEing, by using a hash object, a format, or by creating dynamic code through CALL EXECUTE in a DATA step or SELECT INTO in SQL.
My personal favorite is the hash object:
data lookup;
input codeid $4.;
datalines;
a111
a112
a113
;
data want4;
set have;
if _n_ = 1
then do;
declare hash l (dataset:"lookup");
l.definekey("codeid");
l.definedone();
end;
if l.check() = 0; /* zero means key was found */
run;
Log:
77 data want4;
78 set have;
79 if _n_ = 1
80 then do;
81 declare hash l (dataset:"lookup");
82 l.definekey("codeid");
83 l.definedone();
84 end;
85 if l.check() = 0; /* zero means key was found */
86 run;
NOTE: There were 3 observations read from the data set WORK.LOOKUP.
NOTE: There were 4 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT4 has 3 observations and 1 variables.
The lookup table is sorted in memory into a b-tree, and no sorting has to be done before this step; the order of the "have" dataset is kept. This is the fastest method which SAS provides for lookup tasks. You can even combine an arbitrary number of lookups in one step, as long as the lookups fit into available memory.
Click image to register for webinar
