Creating fake identifiers in Excel or Stata based on matrix

2
votes

I'm trying to create a column of fake identifiers and I'm stuck.

I'm using Excel 2010. I would like to create a column of fake identifiers (1, 2, 3 etc.) based on an "auxiliary matrix". The auxiliary matrix tells me the unique observations and how many times they are repeated.

The auxiliary matrix has two columns and will look like the following. (The actual auxiliary that I have is much larger making it impossible to create manually.)

C1-- C2

3 -- 4

2 -- 6

4 -- 2

The first row of the auxiliary matrix tells me the first 12 observations (3*4) have 4 unique identifiers and each unique observation repeats 3 times before the next unique identifier.

The second row tells me that the next 12 observations (2*6) have 6 unique identifiers and each of them repeats 2 times before the next identifier.

Based on the auxiliary given the final output should be a column like 

1
1
1
2
2
2
3
3
3
.
.
.
11
11
11
11
12
12
12
12

This means that given the auxiliary matrix my final output column should have 12 unique identifiers (4+6+2) and a total of 32 rows (3*4+2*6+4*2).

I also have access to Stata so if anyone has any idea how to accomplish this in Stata it would be great.

1
excelmatrixstatavba

1 Answers

3
votes

I recommend the terminology "distinct" rather than "unique", given the prevailing advice from dictionaries and style guides that "unique" means occurring precisely once.

Here is a Stata solution: 

. clear 

. input c1 c2 

              c1         c2
 1. 3 4
 2. 2 6
 3. 4 2
 4. end 

. gen long seq = _n

. expand c1 * c2
(29 observations created)

. bysort seq : gen work = ceil(_n/c1)

. gen id = sum(work != work[_n-1])

. drop work 

. list 

    +--------------------+
    | c1   c2   seq   id |
    |--------------------|
 1. |  3    4     1    1 |
 2. |  3    4     1    1 |
 3. |  3    4     1    1 |
 4. |  3    4     1    2 |
 5. |  3    4     1    2 |
    |--------------------|
 6. |  3    4     1    2 |
 7. |  3    4     1    3 |
 8. |  3    4     1    3 |
 9. |  3    4     1    3 |
10. |  3    4     1    4 |
    |--------------------|
11. |  3    4     1    4 |
12. |  3    4     1    4 |
13. |  2    6     2    5 |
14. |  2    6     2    5 |
15. |  2    6     2    6 |
    |--------------------|
16. |  2    6     2    6 |
17. |  2    6     2    7 |
18. |  2    6     2    7 |
19. |  2    6     2    8 |
20. |  2    6     2    8 |
    |--------------------|
21. |  2    6     2    9 |
22. |  2    6     2    9 |
23. |  2    6     2   10 |
24. |  2    6     2   10 |
25. |  4    2     3   11 |
    |--------------------|
26. |  4    2     3   11 |
27. |  4    2     3   11 |
28. |  4    2     3   11 |
29. |  4    2     3   12 |
30. |  4    2     3   12 |
    |--------------------|
31. |  4    2     3   12 |
32. |  4    2     3   12 |
    +--------------------+