Desktop productivity for business analysts and programmers

Proc sql with where statement

Accepted Solution Solved
Reply
Contributor
Posts: 62
Accepted Solution

Proc sql with where statement

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.


Accepted Solutions
Solution
‎12-18-2017 01:43 PM
Super User
Posts: 10,530

Re: Proc sql with where statement

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 10,848

Re: Proc sql with where statement

where capacity_refer = '1'

from

sbos_sbl_category

 

===>

 

from

sbos_sbl_category

where capacity_refer = '1'

Super User
Posts: 10,530

Re: Proc sql with where statement

From has to come before the where.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 62

Re: Proc sql with where statement

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;

 

Solution
‎12-18-2017 01:43 PM
Super User
Posts: 10,530

Re: Proc sql with where statement

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 62

Re: Proc sql with where statement

Thanks for the advice, I used or instead and it worked ..
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 309 views
  • 1 like
  • 3 in conversation