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
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.
You can implement data_null_'s option using the Data | Random Sample task in Enterprise Guide.
Tom
Thanks voor the reply's, all work fine.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.