topic Re: Troubleshooting case statement in SAS Procedures

Troubleshooting case statement

Q1983 — Wed, 18 Dec 2013 22:20:43 GMT

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

----

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.

Re: Troubleshooting case statement

ping — Wed, 18 Dec 2013 22:30:13 GMT

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

Re: Troubleshooting case statement

Reeza — Wed, 18 Dec 2013 22:31:24 GMT

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?

Re: Troubleshooting case statement

LarryWorley — Wed, 18 Dec 2013 22:47:12 GMT

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

Re: Troubleshooting case statement

Fugue — Wed, 18 Dec 2013 23:04:14 GMT

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_cd	M17	K16	L39	L96	M60	L58	L44	L52	X
K16	0	31	0	0	0	0	0	0	0
L39	0	0	3	0	0	0	0	0	0
L44	0	0	0	0	0	0	2	0	0
L52	0	0	0	0	0	0	0	99	0
L58	0	0	0	0	0	101	0	0	0
L96	0	0	0	5	0	0	0	0	0
M17	25	0	0	0	0	0	0	0	0
M60	0	0	0	0	17	0	0	0	0
R01	0	0	0	0	0	0	0	0	7
R02	0	0	0	0	0	0	0	0	13
R03	0	0	0	0	0	0	0	0	5
R04	0	0	0	0	0	0	0	0	45
R99	0	0	0	0	0	0	0	0	18

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

Re: Troubleshooting case statement

Tom — Wed, 18 Dec 2013 23:08:47 GMT

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

;

Re: Troubleshooting case statement

Fugue — Thu, 19 Dec 2013 00:15:04 GMT

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;