Proc sql with where statement

[bondtk]

Hi

I am writing this code and it comes up with this error. using where statement with proc sql...where statement is creating error.

Please advise,

proc sql;

16 create table summary5 as

17 select

18 state , capacity_refer

19 ,count(*) as num_apps

20

21 where capacity_refer = '1'

from

sbos_sbl_category

group by

state , capacity_refer

;

quit;

_____

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.

[Ksharp]

where capacity_refer = '1'

from

sbos_sbl_category

===>

from

sbos_sbl_category

where capacity_refer = '1'

[Kurt_Bremser]

From has to come before the where.

[bondtk]

Hi

thanks a lot , it worked. Just a little curious about this one, is there any limitations of where statement.

I used this code 1 worked fine,  code 2 brought zero rows, so not sure what happened, is there any better way to write it. code 2 has two additional variables. so code 2 ha created 6 columns and no rows.  code 1 worked well and created 4 columns and 2 rows of data, so I am confused what happened to code.

code 1:

proc sql;

create table summary6 as

select

app_type_flag, ra01, db23

,count(*) as num_apps

from

sbos_sbl_category

where

db23 = 1 and

ra01 = 1

group by

app_type_flag, ra01, db23

;

quit;

code 2:

proc sql;

create table summary5 as

select

app_type_flag, ra01, db23 , rb21, re51

,count(*) as num_apps

from

sbos_sbl_category

where

db23 = 1 and

ra01 = 1 and

rb21 = 1 and

re51 = 1

group by

app_type_flag, ra01, db23 , rb21 , re51

;

quit;

[Kurt_Bremser · Accepted solution]

There's obviously no observation (row) that meets your expanded where condition. Keep in mind that with "and" all conditions must be true in an observation.

[bondtk]

Thanks for the advice, I used or instead and it worked ..