topic Re: Assign list values to users in a loop in SAS Programming

Assign list values to users in a loop

hporter — Mon, 06 Jun 2022 18:24:14 GMT

Hey All,

I have 2 datasets. One contains around 40k account numbers (all unique), and another contains 30 users (also unique).

I need to distribute account numbers in a particular order to each user evenly. So that by the end of the loop, the volume of accounts with each user is as close as possible.

For simplicity assume these 2 datasets, 1 with 9 account numbers, and 1 with 3 users:

data accounts;
input accts;
cards;
10001
10002
10003
10004
10005
10006
10007
10008
10009
;
run;

data users;
input users;
cards;
1
2
3
;
run;

I want my final output to look like this:

data endResult;
input users accts;
cards;
1 10001
1 10004
1 10007
2 10002
2 10005
2 10008
3 10003
3 10006
3 10009
;
run;

So that, user 1 gets the 1st account number, user 2 gets the 2nd account number, user 3 gets the 3rd account number, then the loop starts over and user 1 gets the next (in this case account number 10004) and then user 2 gets the next after that (10005) and so on and so forth.

I know this can be done in python, and therefore I assume it can be done in SAS with a datastep, but I'm not as familiar with the programming in SAS to accomplish this.

Any one have any ideas? Much appreciated and thanks!

Re: Assign list values to users in a loop

Tom — Mon, 06 Jun 2022 18:32:46 GMT

Pretty trivial with a data step. Just use the POINT= option of the SET statement.

data accounts;
input accts @@;
cards;
10001 10002 10003 10004 10005 10006 10007 10008 10009
;

data users;
  input users @@;
cards;
1 2 3
;

data want;
  set accounts;
  p = 1 + mod(_n_-1,nobs);
  set users point=p nobs=nobs;
run;

proc print;
run;

Or perhaps this method is a little simpler.

data want;
  do p=1 to nobs;
    set accounts;
    set users point=p nobs=nobs;
    output;
  end;
run;

Re: Assign list values to users in a loop

hporter — Mon, 06 Jun 2022 18:42:20 GMT

Can't thank you enough - saved me a lot of headache.

Thanks again!

Re: Assign list values to users in a loop

ballardw — Mon, 06 Jun 2022 18:43:42 GMT

You do not say if these need to be assigned randomly. Is that actually the requirement?