Hi,
Am learning the proc sql with case and where is my code. I want to generate multiple variable (MRI, BIOP & MRI_BIOP) if the condition is true but this is not giving me the expected results. Am help?
proc sql;
create table want as
select *,
case when HCPCS_CD= '55700' | '55705' then 1 else 0 end as BIOP,
case when HCPCS_CD= '77021' then 1 else 0 end as MRI_BIOP ,
case when HCPCS_CD in ('72195', '72196', '72197', '76376', '76377', '76498', '72148', '72146', '72198', '8895', '8896', 'B43', 'B53', 'BW3')
then 1 else 0 end as MRI
from diag_out;
quit;
What do you want to achieve in particular with this:
case when HCPCS_CD= '55700' | '55705
?
I am using "|" to represent "or". I may be wrong that's why I need guidance.
My thought is like using the if then statement
if NCPCS_CD = '55700' or '55705' then BIOp =1; else BIOP = 0;
but i want this is sql for learning purposes
You pretty much need a complete expression after OR
When A='NBC' or A='PDQ'
The IN operator works also;
When A in ('NBC' 'PDQ') ..
What happens when you use something like
NCPCS_CD = '55700' or '55705'
is the value after the OR is expected to be the result of a logical expression. SAS expects such results to be numeric and ideally 0 (false) or 1 (true). So will attempt to convert the value to a numeric and if so uses that result. SAS will use any value other than Missing and zero as true though.
This example shows the result of the comparison:
data junk; NCPCS_CD = '11111'; x = (NCPCS_CD = '55700' or '55705' ); y = (NCPCS_CD = '55700' or NCPCS_CD = '55705' ); z = (NCPCS_CD in ( '55700' '55705') ); run;
The result for X is always 1 (true) because of the conversion of 55705 to numeric.
IF you had used a value that could not be converted to a number in the X expression you would have received an invalid data message:
180 data junk; 181 NCPCS_CD = '11111'; 182 x = (NCPCS_CD = '55700' or 'ABCDE' ); 183 y = (NCPCS_CD = '55700' or NCPCS_CD = '55705' ); 184 z = (NCPCS_CD in ( '55700' '55705') ); 185 run; NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column). 182:29 NOTE: Invalid numeric data, 'ABCDE' , at line 182 column 29. NCPCS_CD=11111 x=0 y=0 z=0 _ERROR_=1 _N_=1 NOTE: The data set WORK.JUNK has 1 observations and 4 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds
Unfortunately Proc SQL is not quite as nice as the data step about generating conversion and invalid data messages.
OR is a binary logical operator which combines logical expressions. So, in your case,
NCPCS_CD = '55700'
is one logical expression, and
'55705'
is considered the other. Can you see your mistake now?
Use IN, like you did in the other CASE.
@CathyVI wrote:
I am using "|" to represent "or". I may be wrong that's why I need guidance.
OR can be replaced by the IN operator, it's less typing.
case when HCPCS_CD in ('55700','55705') then 1 else 0 end as BIOP,
And even less typing, you don't even need CASE WHEN, you don't need THEN and you don't need ELSE and you don't need END
HCPCS_CD in ('55700','55705') as BIOP,
As others have pointed out, you can also do this using OR with the proper syntax (which is more typing):
case when HCPCS_CD= '55700' or HCPCS_CD='55705' then 1 else 0 end as BIOP,
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.