topic Limiting output for each select statement within one create table in SAS Programming

Limiting output for each select statement within one create table

jigsatics — Thu, 02 Mar 2017 15:57:18 GMT

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!

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

collinelliot — Thu, 02 Mar 2017 16:04:35 GMT

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

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

ChrisBrooks — Thu, 02 Mar 2017 16:08:42 GMT

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;

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

jigsatics — Thu, 02 Mar 2017 16:29:38 GMT

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?

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

PGStats — Thu, 02 Mar 2017 17:33:17 GMT

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

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

collinelliot — Thu, 02 Mar 2017 18:09:51 GMT

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