topic Re: using proc sql with case in SAS Programming

using proc sql with case

CathyVI — Thu, 30 Mar 2023 18:36:23 GMT

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;

Re: using proc sql with case

Kurt_Bremser — Thu, 30 Mar 2023 19:59:17 GMT

What do you want to achieve in particular with this:

case when HCPCS_CD= '55700' | '55705

Re: using proc sql with case

CathyVI — Thu, 30 Mar 2023 20:25:04 GMT

@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

Re: using proc sql with case

ballardw — Thu, 30 Mar 2023 20:48:23 GMT

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.

Re: using proc sql with case

Kurt_Bremser — Thu, 30 Mar 2023 20:56:51 GMT

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.

Re: using proc sql with case

PaigeMiller — Thu, 30 Mar 2023 21:01:52 GMT

@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,