BookmarkSubscribeRSS Feed
x2PSx
Calcite | Level 5

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

5 REPLIES 5
Reeza
Super User

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

x2PSx
Calcite | Level 5

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

Reeza
Super User

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
Rhodochrosite | Level 12

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
Opal | Level 21

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 5 replies
  • 8259 views
  • 3 likes
  • 4 in conversation