topic Re: Proc SQL: Limit number of output rows for many subqueries in SAS Programming

Proc SQL: Limit number of output rows for many subqueries

x2PSx — Fri, 10 Nov 2017 20:42:38 GMT

Hello,

I have a table with millions of rows and I want to compare different samples of rows with different conditions to make sure that my code worked properly. I usually use inobs or outobs options with proc sql and it is very handy. Is there something similar that I could use for many subqueries, like if I want for example, only the first 10 rows for each condition only?

I want something similar to this, but select top doesn't work with proc sql as far as I know

proc sql;

Create table WANT as

(select top 10 *

from HAVE

where condition1)

union all

(select top 10 *

from HAVE

where condition2)

union all

(select top 10 *

from HAVE

where condition3)

;

quit;

Thank you very much

Re: Proc SQL: Limit number of output rows for many subqueries

Reeza — Fri, 10 Nov 2017 21:14:16 GMT

Instead of SQL, can you you use a data step? That would allow for such an approach.

Re: Proc SQL: Limit number of output rows for many subqueries

x2PSx — Fri, 10 Nov 2017 21:42:10 GMT

Yes I could use SAS as well, I just tend to use proc sql a lot more

Re: Proc SQL: Limit number of output rows for many subqueries

PGStats — Fri, 10 Nov 2017 21:50:27 GMT

Combine SAS/SQL with SAS dataset options:

proc sql;
create table want as
select * 
from 
(select * from sashelp.class(obs=5 where=(sex="M")))
union all
(select * from sashelp.class(obs=5 where=(sex="F")));
select * from want;
quit;

Re: Proc SQL: Limit number of output rows for many subqueries

Reeza — Fri, 10 Nov 2017 21:52:19 GMT

Top 10 is usually used with an ORDER BY isn't it, to get the top 10 records, otherwise if the data order changes your results change?

Is a random selection what you're after?

Re: Proc SQL: Limit number of output rows for many subqueries

PhilC — Fri, 11 Jun 2021 20:22:20 GMT

ORDER BY in conjunction with the OUTOBS option.

"OUTOBS= restricts the number of rows returned from your query, so outobs = 10 would return only the first 10 rows. The full data is evaluated in the query, but the results set is limited in size, which might save on I/O from a database back to SAS. Hope this helps." Linda - @LAP