BookmarkSubscribeRSS Feed
CathyVI
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10

@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

hackathon24-white-horiz.png

Join the 2025 SAS Hackathon!

Calling all data scientists and open-source enthusiasts! Want to solve real problems that impact your company or the world? Register to hack by August 31st!

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
  • 1214 views
  • 0 likes
  • 4 in conversation