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,
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.