I ran the below to create a computed column and everything worked fine. I then tried to edit this same code in working with data in different data set requiring multiple conditions to be met - meaning when x =1 and y = 1 and z = 1 and aa = 1, then the computed column "Work" but having trouble. How to I use AND for multiple conditions on different data columns? I'm working in SAS EG 7.1
case when XXX_ALL_DIM.lif_IND = 1 then 'Yes'
when YYY_ALL_DIM.lif_IND = 2 then 'No'
when ZZZ_ALL_DIM.lif_IND = 3 then 'Maybe'
else 'Other'
end
Please be a little more specific in presenting the code that works fine, the code that caused the trouble, and what the problem is.
A good starting point would be to explicitly store the results of your CASE expression in a column:
case when XXX_ALL_DIM.lif_IND = 1 then 'Yes'
when YYY_ALL_DIM.lif_IND = 2 then 'No'
when ZZZ_ALL_DIM.lif_IND = 3 then 'Maybe'
else 'Other'
end as MyCategory
This is the code I created for a new column and it worked just fine - but it's more of an If/Then. I'm now working with a different data set and I want to again create a new column that looks at 4 other columns from the data I'm querying and if each, all four of the columns, meets the criteria I have then I want the new column to reflect YES. Even if only one of the columns does does meet the criteria then Other, which would be NO.
case when BRWR_ALL_DIM.WIM_IND = 1 then 'PWM'
when BRWR_ALL_DIM.WIM_IND = 2 then 'WFA'
when BRWR_ALL_DIM.WIM_IND = 3 then 'WFA & PWM'
else 'Other'
end
Yes, the query will work fine, but if you are creating a table and want the column created from the CASE expression to not have a default name then you need to specify what you actually want to call it as in my example.
Regarding using CASE with four input columns - yes that is perfectly feasible. Please post what you have tried so far as the example you have posted doesn't appear to be related to your question.
case
when x = 1 and y = 1 and z = 1 then 1
else 0
end as a
You can use compound conditions in SQL like anywhere else.
Looks like a perfect task for a format.
proc fomat;
value YesNoMaybe
1 = 'Yes'
2 = 'No'
3 = 'Maybe'
other = 'other'
;
run;
proc print data=have;
format lif_IND YesNoMaybe.;
run;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.