Desktop productivity for business analysts and programmers

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

Reply
Occasional Contributor
Posts: 5

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

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,203

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

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.

Respected Advisor
Posts: 3,775

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

proc surveyselect out=sample n=5 seed=1234;
  
strata supplier;
   run;
Trusted Advisor
Posts: 1,051

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

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

Tom

Occasional Contributor
Posts: 5

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

Thanks voor the reply's, all work fine.

Ask a Question
Discussion stats
  • 4 replies
  • 303 views
  • 3 likes
  • 4 in conversation