topic Re: CASE WHEN help in SAS Programming

CASE WHEN help

bhca60 — Wed, 29 Jun 2022 21:17:00 GMT

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;

Re: CASE WHEN help

Reeza — Wed, 29 Jun 2022 21:20:41 GMT

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

And possibly a parenthesis before the list of values:

&cad_suff, &cad_rel)

Re: CASE WHEN help

bhca60 — Wed, 29 Jun 2022 22:41:33 GMT

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')

Re: CASE WHEN help

Tom — Thu, 30 Jun 2022 00:25:00 GMT

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.

Re: CASE WHEN help

bhca60 — Thu, 30 Jun 2022 04:27:37 GMT

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;

Re: CASE WHEN help

SASKiwi — Thu, 30 Jun 2022 04:46:27 GMT

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

Re: CASE WHEN help

Tom — Thu, 30 Jun 2022 04:54:40 GMT

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;

Re: CASE WHEN help

bhca60 — Thu, 30 Jun 2022 14:30:24 GMT

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.

Re: CASE WHEN help

Reeza — Thu, 30 Jun 2022 14:35:26 GMT

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

Re: CASE WHEN help

Tom — Thu, 30 Jun 2022 14:46:11 GMT

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'

Re: CASE WHEN help

bhca60 — Thu, 30 Jun 2022 14:47:23 GMT

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

Re: CASE WHEN help

Reeza — Thu, 30 Jun 2022 15:04:51 GMT

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