Help using Base SAS procedures

Troubleshooting case statement

Reply
Frequent Contributor
Posts: 143

Troubleshooting case statement

proc sql;

create table step_count as

select ls_step_cd,

case when ls_step_cd = 'M17' then count(ln_no)  end as  M17     

  when ls_step_cd = 'K16' then count(ln_no) end as  K16  

when ls_step_cd = 'L39' then count(ln_no) end as  L39 

when ls_step_cd = 'L96' then count(ln_no) end as  L96 

when ls_step_cd = 'M60' then count(ln_no) end as  M60 

when ls_step_cd = 'L58' then count(ln_no) end as  L58  

when ls_step_cd = 'L44' then count(ln_no) end as  L44 

when ls_step_cd = 'L52' then count(ln_no) end as  L52   

else end as  X

from lm_steps

where ls_actual_compl_dt between &pmb and &pme

and ls_step_cd in ('M17','K16','L39','L96','M60','L58','L44','L52')

group by ls_step_cd

order by ls_step_cd;

quit;

I am getting this error

create table step_count as

8149  select ls_step_cd,

8150   case when ls_step_cd = 'M17' then count(ln_no)  end as  M17

8151    when ls_step_cd = 'K16' then count(ln_no) end as  K16

        ----

        22

        76

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, ',', AS, FORMAT, FROM, INFORMAT, INTO, LABEL, LEN, LENGTH, TRANSCODE. 

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

It occurs right after the first when statement.  I have tried several combinations and cannot figure out the issue.

Contributor
Posts: 22

Re: Troubleshooting case statement

add complete CASE WHEN ... THEN statement for each resultant field, and add comma OR use select () statement

Super User
Posts: 19,860

Re: Troubleshooting case statement

select ls_step_cd,

case when ls_step_cd = 'M17' then count(ln_no)  end as  M17     

case when ls_step_cd = 'K16' then count(ln_no) end as  K16  

case when ls_step_cd = 'L39' then count(ln_no) end as  L39 

case when ls_step_cd = 'L96' then count(ln_no) end as  L96 

case when ls_step_cd = 'M60' then count(ln_no) end as  M60 

case when ls_step_cd = 'L58' then count(ln_no) end as  L58  

case when ls_step_cd = 'L44' then count(ln_no) end as  L44 

case when ls_step_cd = 'L52' then count(ln_no) end as  L52   

else end as  X /*need a case statement*/


I don't think this will do what you want though, it looks like you're trying to transpose so why not use proc transpose instead?

Frequent Contributor
Posts: 129

Re: Troubleshooting case statement

Or are you trying to get something like this:

ls_step_cd    number_of_occurences

M17                5

K16              10

and so on

If so restructure your sql as

create table step_count as

select ls_step_cd,

      count(ln_no)  as number_of_occurances    

from lm_steps

where ls_actual_compl_dt between &pmb and &pme

and ls_step_cd in ('M17','K16','L39','L96','M60','L58','L44','L52')

group by ls_step_cd

order by ls_step_cd;

Note it always help to tell what you are trying to do.  Syntactically, the 'end' key word closes a case statement.  So after the SQL engine found 'as M17' it as looking for  what can come next legally.  For example, a new field in the select statement ==> ',' or column modifier like FORMAT or LENGTH, etc.

HTH

Super Contributor
Posts: 307

Re: Troubleshooting case statement

Looks like Q1983 is trying to create a matrix, that would look something like this (I've added some fictional ls_step_cd codes and bolding for emphasis):

 

ls_step_cdM17K16L39L96M60L58L44L52X
K160310000000
L39003000000
L44000000200
L520000000990
L5800000101000
L96000500000
M172500000000
M600000170000
R01000000007
R020000000013
R03000000005
R040000000045
R990000000018

 

Reeza's code needs a few small tweaks:

case when ls_step_cd = 'M17' then count(ln_no) else 0 end as  M17

case when ls_step_cd = 'K16' then count(ln_no) else 0 end as  K16.

. . . so on and so forth . . .

/* case statement to deal with all other codes -- assigned to X bucket */

CASE when ln_step_cd not in ( 'M17', 'K16', 'L39', 'L96', 'M60', 'L58', 'L44', 'L52' ) then count ( ln_no ) else 0 end as  X

Super User
Super User
Posts: 7,076

Re: Troubleshooting case statement

One way to do this is as a subquery to generate boolean flags that you can then sum to get your counts..

create table step_count as

select ls_step_cd

      , sum(M17) as M17

      , sum(K16) as K16

from

  (  select ls_step_cd

    , case when ls_step_cd = 'M17' then 1 else 0 end as  M17    

   , case when ls_step_cd = 'K16' then 1 else 0 end as  K16 

     from lm_steps

     where ls_actual_compl_dt between &pmb and &pme

       and ln_no is not null

       and ls_step_cd in ('M17','K16')

   )

group by ls_step_cd

order by ls_step_cd

;

Super Contributor
Posts: 307

Re: Troubleshooting case statement

Could also do the following (if the intent is to produce a matrix), assuming ln_no exists on every row of the input data -- this eliminates the case statements entirely:

proc sql;

create table step_count as

select ls_step_cd

, sum (ls_step_cd = 'M17') as M17

, sum (ls_step_cd = 'K16') as K16

, sum (ls_step_cd = 'L39') as L39

, sum (ls_step_cd = 'L96') as L96

, sum (ls_step_cd = 'M60') as M60

, sum (ls_step_cd = 'L58') as L58  

, sum ( ls_step_cd = 'L44') as L44

, sum (ls_step_cd = 'L52') as L52

/* everything else bucket -- results will vary if where clause is changed */

, sum (ls_step_cd not in ('M17', 'K16', 'L39', 'L96', 'M60', 'L58', 'L44', 'L52')) as  X

from lm_steps

where ls_actual_compl_dt between &pmb and &pme

      and ls_step_cd in ('M17','K16','L39','L96','M60','L58','L44','L52')

group by ls_step_cd

order by ls_step_cd;

quit;

Ask a Question
Discussion stats
  • 6 replies
  • 262 views
  • 0 likes
  • 6 in conversation