Proc SQL: Limit number of output rows for many subqueries

[x2PSx]

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

[Reeza]

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

[x2PSx]

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

[Reeza]

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?

[PhilC]

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

Related post: Solved: How to select first few records to test the Proc s... - SAS Support Communities 

[PGStats]

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;