sql warning

Accepted Solution Solved
Reply
Regular Contributor
Posts: 158
Accepted Solution

sql warning

[ Edited ]

I'm trying the following sql code and it gave me this warning message when I limit the outputs to 3  

Is this warning ok?? 

 

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
70
71 proc sql outobs=3;
72 create table &Queries.3 as
73 select category, count(*) as cat
74 from &Queries.1
75 where status="Open"
76 group by 1
77 order by 2 desc;
WARNING: Statement terminated early due to OUTOBS=3 option.
NOTE: Table WORK.QUERIESN3 created, with 3 rows and 2 columns.
 
78 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 6269.89k
OS Memory 36028.00k
Timestamp 03/06/2018 02:36:18 PM
Step Count 205 Switch Count 2
Page Faults 0
Page Reclaims 634
Page Swaps 0
Voluntary Context Switches 22
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 272
 
 
79
80
81 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
93

Accepted Solutions
Solution
‎03-06-2018 12:42 PM
Super User
Super User
Posts: 9,599

Re: sql warning

You have told the code to finish at 3 observations regardless of how many it would normally of done, the warning confirms that this is what has happened so that there is no misconceptions further along.  Why do you need to artificially limit the obs to 3 anyway?  Why can you not logically arrive at 3 records?

View solution in original post


All Replies
Solution
‎03-06-2018 12:42 PM
Super User
Super User
Posts: 9,599

Re: sql warning

You have told the code to finish at 3 observations regardless of how many it would normally of done, the warning confirms that this is what has happened so that there is no misconceptions further along.  Why do you need to artificially limit the obs to 3 anyway?  Why can you not logically arrive at 3 records?

Regular Contributor
Posts: 158

Re: sql warning

I just want to output the highest 3
Super User
Posts: 10,209

Re: sql warning

Move the limitation from SQL to a dataset option:

proc sql;
create table &Queries.3 (obs=3) as
select category, count(*) as cat
from &Queries.1
where status="Open"
group by 1
order by 2 desc;
quit;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 158

Re: sql warning

Posted in reply to KurtBremser
1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
70
71 proc sql ;
72 create table &Queries.2 (obs=3) as
73 select site_number, count(*) as sitenum
74 from &Queries.1
75 where status="Open"
76 group by site_number
77 order by sitenum desc;
WARNING: The option OBS is not valid in this context. Option ignored.
NOTE: Table WORK.QUERIESN2 created, with 10 rows and 2 columns.
 
78 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 5774.45k
OS Memory 36012.00k
Timestamp 03/06/2018 02:58:58 PM
Step Count 277 Switch Count 2
Page Faults 0
Page Reclaims 636
Page Swaps 0
Voluntary Context Switches 21
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 272
PROC Star
Posts: 508

Re: sql warning

just change @KurtBremser code to

proc sql outobs=3;
create table class as 
select * from sashelp.class
order by height desc;
Regular Contributor
Posts: 158

Re: sql warning

that's what I did but it gave me warning 

Super User
Posts: 10,209

Re: sql warning

That was just a shot into the blue.

 

For production purposes (no warnings allowed), I'd run the SQL without outobs, and then filter the first three entries in a follow-up step.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Super User
Posts: 9,599

Re: sql warning

PROC Star
Posts: 2,321

Re: sql warning

[ Edited ]

proc sql nowarn outobs=3;

 

This is the most efficient way to limit the output.

 

proc sql will stop processing data as soon as the outobs= condition is met.

 

☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 352 views
  • 11 likes
  • 5 in conversation