BookmarkSubscribeRSS Feed
CathyVI
Pyrite | Level 9

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;

5 REPLIES 5
CathyVI
Pyrite | Level 9

@Kurt_Bremser 

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

ballardw
Super User

 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.

 

 

Kurt_Bremser
Super User

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.

PaigeMiller
Diamond | Level 26

@CathyVI wrote:

@Kurt_Bremser 

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,

 

--
Paige Miller

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 867 views
  • 0 likes
  • 4 in conversation