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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.