CASE WHEN help

[bhca60]

I am having issues with CASE WHEN. I'm trying to create a column called Condition and flag each as asthma, COPD, etc.  I have two sets of codes for each condtion labeled as _suff or _rel (sufficient or related, respectively).

23         proc sql;
24         create table condition as
25         select
26         id,
27         pid,
28         dxCode as icd,
29         dxCodeDescription,
30         dxSeqNo
31         case when (compress(icd,".") in (&asthma_suff, &asthma_rel) then 'ASTHMA'
           ____
           22
           76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =, 
              >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, 
              LEN, LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.  

ERROR 76-322: Syntax error, statement will be ignored.

32         
33          	 when (compress(icd,".") in &cad_suff, &cad_rel) then 'CAD'
34         
35              when (compress(icd,".") in &chf_suff, &chf_rel)  then 'CHF'
36         	
37          	 when (compress(icd,".") in &copd_suff, &copd_rel) then 'COPD'
38         
39          	 when (compress(icd,".") in &diab_suff, &diab_rel) then 'DIABETES'
40         
41         else 'HTN'
42         end as Condition
43         from dxtable
44         order by eventid;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
45         quit;

[Reeza]

You forgot a comma before the CASE (after dxSeqNo).

And possibly a parenthesis before the list of values: 

&cad_suff, &cad_rel)

[bhca60]

still not working. I tried doing OR operators but it doesnt work for CASE WHEN.

the macros are a list of codes looking like this: ('J9088','90877')

[Tom]

Get the syntax right before adding the code generation (macro code).

You are missing commas and parentheses.  Why are you ordering by a variable that is not being selected?

proc sql;
create table condition as
  select
     id
   , pid
   , dxCode as icd
   , dxCodeDescription
   , dxSeqNo
   , case when ( compress(icd,".") in ('A') ) then 'ASTHMA'
          when ( compress(icd,".") in ('B') ) then 'CAD'
          when ( compress(icd,".") in ('C') ) then 'CHF'
          when ( compress(icd,".") in ('D') ) then 'COPD'
          when ( compress(icd,".") in ('E') ) then 'DIABETES'
          else 'HTN'
     end as Condition
  from dxtable
  order by id
;
quit;

Then you can try adding in your macro variable references.

[bhca60]

I'm getting an error (the parentheses seem fine now):

23         proc sql;
24         create table condition as
25         select
26         id,
27         pid,
28         dxCode as icd,
29         dxCodeDescription,
30         dxSeqNo,
31         case when (compress(icd,".") in (&asthma_suff)) then 'ASTHMA'
NOTE: Line generated by the macro variable "ASTHMA_SUFF".
31         ('J4520','J4521','J4522','J4530','J4531','J4532','J4540','J4541','J4542','J4550','J4551','J4552','J45901','J45902','J4590
            _______
_                                                                                                                                   
            79
22

76
31       ! 9','J45990','J45991','J45998')
ERROR 79-322: Expecting a SELECT.

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, 
              AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH, 
              LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.  

ERROR 76-322: Syntax error, statement will be ignored.

32         	 when (compress(icd,".") in (&asthma_rel)) then 'ASTHMA'
33          	 when (compress(icd,".") in (&cad_suff)) then 'CAD'
34         	 when (compress(icd,".") in (&cad_rel)) then 'CAD'
35              when (compress(icd,".") in (&chf_suff)  then 'CHF'
36         	 when (compress(icd,".") in (&chf_rel)) then 'CHF'
37          	 when (compress(icd,".") in (&copd_suff) then 'COPD'
38           	 when (compress(icd,".") in (&copd_rel)) then 'COPD'
39          	 when (compress(icd,".") in (&diab_suff)) then 'DIABETES'
40          	 when (compress(icd,".") in (&diab_rel)) then 'DIABETES'
2                                                          The SAS System                             20:52 Wednesday, June 29, 2022

41         else 'HTN'
42         end as Condition
43         from dxtable
44         order by id;
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
45         quit;

[SASKiwi]

Looks like your IN list is enclosed in two lots of brackets. Removing the brackets from your macro variable value is probably the best option.

 get rid of this bracket ==>('J4520','J4521','J4522','J4530','J4531','J4532','J4540','J4541','J4542','J4550','J4551','J4552','J45901','J45902','J4590
            _______
_                                                                                                                                   
            79
22

76
31       ! 9','J45990','J45991','J45998') <== get rid of this bracket

[Tom]

You have accidentally added macro quoting to the value of your macro variable.  Remove it.

case when (compress(icd,".") in (%unquote(&asthma_suff) )) 

Why did you add the macro quoting?

To be able to use the value of those macro variables to generate that code they cannot contain any characters that would need macro quoting. 

Example:

11   %let x='A','B';
12   proc sql;
13   select name
14        , case when (name in (&x)) then 'xxx' end as junk
15   from sashelp.class
16   ;
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE
      expression.
NOTE: Writing HTML Body file: sashtml.htm
17   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.14 seconds
      cpu time            0.12 seconds


18   %let x=%bquote('A','B');
19   proc sql;
20   select name
21        , case when (name in (&x)) then 'xxx' end as junk
NOTE: Line generated by the macro variable "X".
1    'A','B'
     -
     22
      -
      200
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant,
              a missing value, (, -, SELECT.

ERROR 200-322: The symbol is not recognized and will be ignored.

22   from sashelp.class
23   ;
24   quit;

[bhca60]

are you saying to get rid of the parentheses? I'm going to try that and see if it works. But I was using those macros throughout my program. I will have to do another set of macros wihtout the parentheses then rename them then call them when doing the CASE WHEN statement.

[Reeza]

---

@bhca60 wrote:
are you saying to get rid of the parentheses? I'm going to try that and see if it works. But I was using those macros throughout my program. I will have to do another set of macros wihtout the parentheses then rename them then call them when doing the CASE WHEN statement.

---

If you have parenthesis in this statement I'm sure you can see how it would error out.

 &cad_suff, &cad_rel

Since that would resolve to:

((list of codes), (list of codes))

You could also just expand your case when statements so that you have each as a separate condition.

when (compress(icd,".") in &cad_suff or compress(icd,".") in &cad_rel then "CAD"

[bhca60]

"Or" doesnt work in case when; i had it like that before and it gave me error

[Reeza]

---

@bhca60 wrote:
"Or" doesnt work in case when; i had it like that before and it gave me error

---

proc sql;
create table demo as
select *, case when age = 13 or age=15 or age=11 then 'Select'
else 'Not Selected' end as check
from sashelp.class;
quit;

proc print data=demo;
run;

OR does work, something else was the issue. 

[Tom]

If the macro variables already have the () then don't add extra ones.

when (compress(icd,".") in &cad_suff) then 'CAD'
when (compress(icd,".") in &cad_rel ) then 'CAD'