topic Proc sql with where statement in SAS Enterprise Guide

Proc sql with where statement

bondtk — Sat, 16 Dec 2017 09:54:08 GMT

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

21 where capacity_refer = '1'

from

sbos_sbl_category

group by

state , capacity_refer

;

quit;

_____

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.

Re: Proc sql with where statement

Ksharp — Sat, 16 Dec 2017 10:36:34 GMT

where capacity_refer = '1'

from

sbos_sbl_category

===>

from

sbos_sbl_category

where capacity_refer = '1'

Re: Proc sql with where statement

Kurt_Bremser — Sat, 16 Dec 2017 14:40:35 GMT

From has to come before the where.

Re: Proc sql with where statement

bondtk — Sun, 17 Dec 2017 01:29:08 GMT

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;

Re: Proc sql with where statement

Kurt_Bremser — Sun, 17 Dec 2017 07:08:48 GMT

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.

Re: Proc sql with where statement

bondtk — Mon, 18 Dec 2017 18:45:19 GMT

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