Solved
Contributor
Posts: 52

# Random selection of rows from various datasets

I have a dataset containing 20 User Logins. (Only Var = Login)

I have 2 other datasets each containing 1 var.

PWSource1 contains 50 freshly prepared Passwords in a var called PW1.

PWSource2 contains about 90 other freshly prepared Passwords in a Var called PW2.

I need to select the Login from UserLogins (in=a) and then a random passw from PWSource1 (from PW1) and a second random password from PWSource2.

For each Login I will combine PW1 and PW2 to make a single Password in my final output.

Has anyone got some sample code or suggestions on how to code the random selection of each Password per Login?

Any help will be much appreciated...

:-)

Len

Accepted Solutions
Solution
‎09-06-2017 04:39 AM
Super User
Posts: 6,935

## Re: Random selection of rows from various datasets

To add some detail to @Bruno_SAS suggestion:

data want;

set users;

recno = ceil(n_pw1 * ranuni(12345));

set pw1 nobs=n_pw1 point=recno;

recno = ceil(n_pw2 * ranuni(97531));

set pw1 nobs=n_pw2 point=recno;

run;

All Replies
SAS Super FREQ
Posts: 830

## Re: Random selection of rows from various datasets

You can use the POINT= options of the SET statement to randomly read an observation. To calculate the random observation number you can use the RANUNI function together with the CEIL function.

The NOBS= option on the SET statement will return the total number of observations in SAS data set.

The DATA step will read the data set with all the logins, you then calculate a random number to read from the password data set

Solution
‎09-06-2017 04:39 AM
Super User
Posts: 6,935

## Re: Random selection of rows from various datasets

To add some detail to @Bruno_SAS suggestion:

data want;

set users;

recno = ceil(n_pw1 * ranuni(12345));

set pw1 nobs=n_pw1 point=recno;

recno = ceil(n_pw2 * ranuni(97531));

set pw1 nobs=n_pw2 point=recno;

run;

Contributor
Posts: 52

## Re: Random selection of rows from various datasets

Thank you Astounding! this is perfect. much appreciated
Super User
Posts: 6,935

## Re: Random selection of rows from various datasets

[ Edited ]

It takes a slightly different approach to make sure passwords are not reused.

proc sql noprint;

create table password_list as select * from pw1, pw2;

quit;

random_order = ranuni(12345);

run;

by random_order;

run;

data want;

set users;

run;

The solution from @PGStats may be successful.  It will limit the number of passwords that can be assigned to 50 (the smaller number from the PW1 and PW2 data sets).

Super User
Posts: 24,026

Contributor
Posts: 52

## Re: Random selection of rows from various datasets

Hi Reeza

Passwords can be reused, but each combination of PW1 + PW2 should be unique:

A + M = AM  >> OK

A + N = AN  >> OK

B + M = BM >> OK

C + N = CN >> OK

A + M = AM >> Not OK as it is already used

Posts: 5,626

## Re: Random selection of rows from various datasets

If you want to make sure that the passwords are not reused then you could use this method:

``````data rLogins;
rnd = rand("UNIFORM");
run;

data rPW1;
set PW1;
rnd = rand("UNIFORM");
run;

data rPW2;
set PW2;
rnd = rand("UNIFORM");
run;

proc sort data=rLogins; by rnd; run;
proc sort data=rPW1; by rnd; run;
proc sort data=rPW2; by rnd; run;

data want;