DATA Step, Macro, Functions and more

Proc SQL: Limit number of output rows for many subqueries

Reply
Occasional Contributor
Posts: 15

Proc SQL: Limit number of output rows for many subqueries

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

Super User
Posts: 19,822

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

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

Occasional Contributor
Posts: 15

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

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

Super User
Posts: 19,822

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

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?

Respected Advisor
Posts: 4,927

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

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;

PG
Ask a Question
Discussion stats
  • 4 replies
  • 93 views
  • 2 likes
  • 3 in conversation