Hi,
ID GRP
0001 A
0002 A
0003 A
0004 B
0005 B
0006
0007 B
0008 A
0009
0010
0011 B
0012
0013 D
0014 B
0015
0016 A
0017
0018 A
0019
0020
0021 C
0022
0023
0024 C
0001
0002
0003
0004
0005
I have the dataset above - I want to distribute the IDs to GRP (A or B) given the following conditions.
- If the repeated ID value has no assigned GRP then check from other observation if it has GRP value (A or B) and assign the GRP value, if no then assign new GRP.
- Distribute the unassigned IDs to GRP (A or B ) given that the GRP must have equal or at least near number of assigned IDs.
I know this can be done using loop in SAS but I don't know how to distribute the ID values after counting or getting the ID count on each group
Below is the desired output; the highlighted Yellow GRPs were IDs that were previously assigned and the Blue are the new assigned IDs to GRPs.
The table on the right side explains the distribution of remaining IDs to the GRP.
Thank you!
There are some uncertainties in what you have posted so I won't convert your numbers into a data step and write code.
In general, it occurs to me that you have two kinds of records; 1) those with an id and a grp and 2) those with just an id. Within the second group you also have two kinds of records, 3) those with an id that matches a record with that id that has a grp, and 4) those that do not match a record on id.
1 is done.
3 is a match merge, find records that match the id in question and give them the group from the matching record.
4 strikes me as something that might could be done with proc surveyselect. It will certainly randomly assign essentially equal groups to all the unassigned ids.
Whether you would then need to do some additional evening out of group numbers I couldn't say.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.