Google SAS Search

Add to Google

Wednesday, November 14, 2007

Using Logical Expressions In SQL

Most of us SAS programmers approach SQL as simply a data extraction and table joining tool. Since most of us have used the data step longer than SQL, we tend to leave the logic programming to the data step with its if/then statements. However, SQL does have a way of assigning values conditionally. With the CASE expression you can test and assign values logically.
The basic syntax is:


CASE value
WHEN condition THEN result
WHEN condition THEN result
ELSE result
END



In the code below I am just assigning a 1 or a 0 to a column/variable named bool_tf.
Using the CASE expression is pretty straightforward and is another great way to use SQL to get more coding done in fewer steps.



data myData;
input answer $;
datalines;
true
false
true
true
false
false
true
;
proc sql;
create table a as
select answer,
case substr(answer,1,1)
when 't' then 1
when 'f' then 0
end as bool_tf
from myData;
quit;

1 comment:

  1. Very helpful. I do tend to do that sort of thing in the data step not being much of an SQL person myself.

    Sassy Grrrl

    ReplyDelete