topic Re: Random (aselect) select X number of customers for each supplier in SAS Enterprise Guide

Random (aselect) select X number of customers for each supplier

HKlaassen — Thu, 13 Mar 2014 10:31:20 GMT

Hello

I would like to random select customernumber for each supplier in my database. By example i would like to see five customernumber for each supplier, and the selection of those five customernumber has to be random.

How can i do this in Enterprise Guide?

Exmple dataset:

supplier customernumber

A 10002

A 10003

A 10004

A 10005

A 10006

A 10007

A 10008

A 10009

A 10010

A 10011

B 10012

B 10013

B 10014

B 10015

B 10016

B 10017

B 10018

B 10019

B 10020

B 10021

and more

Example Result

supplier customernumber

A 10003

A 10005

A 10007

A 10008

A 10010

B 10012

B 10014

B 10015

B 10017

B 10021

Re: Random (aselect) select X number of customers for each supplier

RW9 — Thu, 13 Mar 2014 11:18:38 GMT

Hi,

Given your data above, you could do (this is base SAS not Enterprise specific so hope it helps):

proc sql;

create table EXAMPLE_RESULT as

select SUPPLIER,

CUSTOMERNUMBER,

int(ranuni(4321)*10) as RAND_NUM

from EXAMPLE_DATASET

order by SUPPLIER,

RAND_NUM;

quit;

data example_result (drop=i rand_num);

set example_result;

by supplier;

retain i;

if first.supplier then i=1;

if i<=5 then output;

i=i+1;

run;

The proc sql statement assigns a random number between 1-10 to each row, then sorts the dataset by supplier and this random number which gets the data all mixed up per supplier.

The datastep then outputs the first five records per supplier.

Re: Random (aselect) select X number of customers for each supplier

data_null__ — Thu, 13 Mar 2014 13:18:46 GMT

proc surveyselect out=sample n=5 seed=1234; 
   strata supplier;
   run; 

Re: Random (aselect) select X number of customers for each supplier

TomKari — Thu, 13 Mar 2014 16:52:54 GMT

You can implement data_null_'s option using the Data | Random Sample task in Enterprise Guide.

Tom

Re: Random (aselect) select X number of customers for each supplier

HKlaassen — Mon, 17 Mar 2014 08:33:33 GMT

Thanks voor the reply's, all work fine.