DATA Step, Macro, Functions and more

Limiting output for each select statement within one create table

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Limiting output for each select statement within one create table

Hi,

 

I have a SAS code that looks like this:

PROC SQL;
create table CombinedSample as
	(select account_number, 'Cell1' as seed 
		from Dataset1
	where age between 16 and 34)

union 

(select account_number, 'Cell2' as seed
		from Dataset1
	where age between 35 and 54)
;
QUIT;

I have 10 or more similar select statements that will be included in the code. My question is how can I limit the output for each  select statement?  For example, for Cell1 i only need 50 and for Cell2 128, etc.

 

Someone here used limit statements but it only works on AQT.

 

TIA!


Accepted Solutions
Solution
‎03-02-2017 05:09 PM
Respected Advisor
Posts: 4,659

Re: Limiting output for each select statement within one create table

Do it after, with surveyselect. Stratify with seed. You will have full control of sample sizes and the assurance that observations are selected randomly.

PG

View solution in original post


All Replies
PROC Star
Posts: 290

Re: Limiting output for each select statement within one create table

And you don't care which rows they are, as long as there are only 50, for example, in the first query?

 

I don't know what all the other queries look like, but for what you've shown, try adding the following to the where statement:

 

PROC SQL;
create table CombinedSample as
	(select account_number, 'Cell1' as seed 
		from Dataset1
	where age between 16 and 34 AND
                  monotonic() <= 50)

union 
New Contributor
Posts: 2

Re: Limiting output for each select statement within one create table

The other queries will be similar to the first 2 but with a slightly different where statement. The idea is to get a random 50. Does the monotonic function randomly pick 50?

PROC Star
Posts: 290

Re: Limiting output for each select statement within one create table

Given what you want, PGStats approach is the right way to go.

Regular Contributor
Posts: 190

Re: Limiting output for each select statement within one create table

Try the inobs= or outobs= options e.g. 

 

proc sql outobs=3;
	create table classmall
	as select *
	from sashelp.class
	where sex='M';
quit;

proc sql inobs=3;
	create table classmall
	as select *
	from sashelp.class
	where sex='M';
quit;
Solution
‎03-02-2017 05:09 PM
Respected Advisor
Posts: 4,659

Re: Limiting output for each select statement within one create table

Do it after, with surveyselect. Stratify with seed. You will have full control of sample sizes and the assurance that observations are selected randomly.

PG
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 119 views
  • 1 like
  • 4 in conversation