topic Re: Random selection of rows from various datasets in SAS Programming

Random selection of rows from various datasets

Lenvdb — Tue, 05 Sep 2017 15:43:01 GMT

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

Re: Random selection of rows from various datasets

BrunoMueller — Tue, 05 Sep 2017 16:11:02 GMT

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

Re: Random selection of rows from various datasets

Astounding — Tue, 05 Sep 2017 16:14:59 GMT

To add some detail to @BrunoMueller 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;

Re: Random selection of rows from various datasets

Reeza — Tue, 05 Sep 2017 17:14:31 GMT

Can the passwords be reused?

Re: Random selection of rows from various datasets

PGStats — Tue, 05 Sep 2017 22:37:31 GMT

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

data rLogins;
set logins;
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;
set rLogins;
set rPW1;
set rPW2;
run;

Re: Random selection of rows from various datasets

Lenvdb — Wed, 06 Sep 2017 08:08:15 GMT

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

Re: Random selection of rows from various datasets

Lenvdb — Wed, 06 Sep 2017 08:40:55 GMT

Thank you Astounding! this is perfect. much appreciated

Re: Random selection of rows from various datasets

Astounding — Wed, 06 Sep 2017 09:20:28 GMT

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;

data password_list2;

set password_list;

random_order = ranuni(12345);

run;

proc sort data=password_list2;

by random_order;

run;

data want;

set users;

set password_list2 (drop=random_order);

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).