BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mona4u
Lapis Lazuli | Level 10

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
1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

mona4u
Lapis Lazuli | Level 10
I just want to output the highest 3
Kurt_Bremser
Super User

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;
mona4u
Lapis Lazuli | Level 10
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
kiranv_
Rhodochrosite | Level 12

just change @Kurt_Bremser code to

proc sql outobs=3;
create table class as 
select * from sashelp.class
order by height desc;
mona4u
Lapis Lazuli | Level 10

that's what I did but it gave me warning 

Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26
ChrisNZ
Tourmaline | Level 20

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 9 replies
  • 3947 views
  • 11 likes
  • 5 in conversation