BookmarkSubscribeRSS Feed
Q1983
Lapis Lazuli | Level 10

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.

6 REPLIES 6
ping
Fluorite | Level 6

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

Reeza
Super User

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?

LarryWorley
Fluorite | Level 6

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

Fugue
Quartz | Level 8

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

Tom
Super User Tom
Super User

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

;

Fugue
Quartz | Level 8

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1012 views
  • 0 likes
  • 6 in conversation