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.
add complete CASE WHEN ... THEN statement for each resultant field, and add comma OR use select () 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?
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
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
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
;
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.