Generating columns for each unique value within a group

[Will Fannin]

Hello,

I have monthly individual program data with the following variables: individual identifiers (id), month-year (modate), and household ID (case). The case numbers can vary within each ID.

I would like to create columns for each unique case number within each individual identifier. I have manually added what I am looking for as NEW1 and NEW2. I only have two new variables in this example because the max number of unique cases within each id is two, but with the full dataset I want as many new variables as there are unique cases within each ID.

* Example generated by -dataex-. For more info, type help dataex
clear
input long id float modate str9(case NEW1 NEW2)
1 684 "110" "110" "114"
1 685 "110" "110" "114"
1 686 "110" "110" "114"
1 687 "110" "110" "114"
1 688 "110" "110" "114"
1 689 "110" "110" "114"
1 690 "110" "110" "114"
1 691 "110" "110" "114"
1 692 "110" "110" "114"
1 693 "110" "110" "114"
1 694 "110" "110" "114"
1 695 "110" "110" "114"
1 696 "110" "110" "114"
1 697 "110" "110" "114"
1 698 "114" "110" "114"
1 699 "114" "110" "114"
1 700 "114" "110" "114"
1 701 "114" "110" "114"
1 702 "114" "110" "114"
1 703 "114" "110" "114"
1 704 "114" "110" "114"
1 705 "114" "110" "114"
1 706 "114" "110" "114"
1 707 "114" "110" "114"
2 684 "88"  "88"  ""   
2 685 "88"  "88"  ""   
2 686 "88"  "88"  ""   
2 687 "88"  "88"  ""   
2 688 "88"  "88"  ""   
2 689 "88"  "88"  ""   
2 690 "88"  "88"  ""   
2 691 "88"  "88"  ""   
2 692 "88"  "88"  ""   
2 693 "88"  "88"  ""   
2 694 "88"  "88"  ""   
2 695 "88"  "88"  ""   
2 696 "88"  "88"  ""   
2 697 "88"  "88"  ""   
2 698 ""    "88"  ""   
2 699 ""    "88"  ""   
2 700 ""    "88"  ""   
2 701 ""    "88"  ""   
2 702 ""    "88"  ""   
2 703 ""    "88"  ""   
2 704 ""    "88"  ""   
2 705 ""    "88"  ""   
2 706 ""    "88"  ""   
2 707 ""    "88"  ""   
end
format %tm modate

[Clyde Schechter]

* Example generated by -dataex-. For more info, type help dataex
clear*
input long id float modate str9 case
1 684 "110"
1 685 "110"
1 686 "110"
1 687 "110"
1 688 "110"
1 689 "110"
1 690 "110"
1 691 "110"
1 692 "110"
1 693 "110"
1 694 "110"
1 695 "110"
1 696 "110"
1 697 "110"
1 698 "114"
1 699 "114"
1 700 "114"
1 701 "114"
1 702 "114"
1 703 "114"
1 704 "114"
1 705 "114"
1 706 "114"
1 707 "114"
2 684 "88"
2 685 "88"
2 686 "88"
2 687 "88"
2 688 "88"
2 689 "88"
2 690 "88"
2 691 "88"
2 692 "88"
2 693 "88"
2 694 "88"
2 695 "88"
2 696 "88"
2 697 "88"
2 698 ""  
2 699 ""  
2 700 ""  
2 701 ""  
2 702 ""  
2 703 ""  
2 704 ""  
2 705 ""  
2 706 ""  
2 707 ""  
end
format %tm modate

frame put id  case, into(working)
frame working {
    duplicates drop
    sort id, stable
    by id: gen long seq = _n
    reshape wide case, i(id) j(seq)
    rename case* new*
}

frlink m:1 id, frame(working)
frget new*, from(working)
drop working
frame drop working

Added: I am struck by the oddness of the arrangement you are asking for. You will be associating observations of an id on a given date with values of a variable that exist only on other dates, creating the illusion that they are synchronically connected. Moreover, each of the new "variables" is, in fact, a constant. And when one sees constant variables, it is always worth questioning whether something is being done wrong. (Yes, there are sometimes good uses for them, but not very often.) So I'm curious what you will use this for.