BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
hporter
Obsidian | Level 7

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;

Tom_0-1654540228632.png

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;

View solution in original post

3 REPLIES 3
Tom
Super User Tom
Super User

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;

Tom_0-1654540228632.png

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;
hporter
Obsidian | Level 7

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

 

Thanks again!

ballardw
Super User

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

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 370 views
  • 1 like
  • 3 in conversation