topic Re: sql warning in SAS Programming

sql warning

mona4u — Tue, 06 Mar 2018 14:36:46 GMT

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;

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

81 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Re: sql warning

RW9 — Tue, 06 Mar 2018 14:42:53 GMT

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?

Re: sql warning

mona4u — Tue, 06 Mar 2018 14:45:11 GMT

I just want to output the highest 3

Re: sql warning

Kurt_Bremser — Tue, 06 Mar 2018 14:50:26 GMT

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;

Re: sql warning

RW9 — Tue, 06 Mar 2018 14:55:25 GMT

Alternatively you can also use proc means to do this:

http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a001016379.htm

Re: sql warning

mona4u — Tue, 06 Mar 2018 14:59:45 GMT

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

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

Re: sql warning

kiranv_ — Tue, 06 Mar 2018 15:33:02 GMT

just change @Kurt_Bremser code to

proc sql outobs=3;
create table class as 
select * from sashelp.class
order by height desc;

Re: sql warning

mona4u — Tue, 06 Mar 2018 15:42:44 GMT

that's what I did but it gave me warning

Re: sql warning

Kurt_Bremser — Tue, 06 Mar 2018 16:21:36 GMT

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.

Re: sql warning

ChrisNZ — Thu, 08 Mar 2018 03:36:07 GMT

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.