Append one variable after another in the same dataset

[Zihan Dong]

Hello,

This is part of my dataset.

What I want to do is to append contri_dia2 contri_dia3 contri_dia4 contri_dia5 under contri_dia1, so that I could get a new variable contri_dia (the final dataset is expected to be 2 colunms: lopnr and contri_dia).

As id (in my case "lopnr") is not unique identifier in my dataset, so I can't use reshape...

Could anyone help me with this question? Thank you in advance!

Best,
Z

* Example generated by -dataex-. To install: ssc install dataex
clear
input long lopnr str6 contri_dia1 str7 contri_dia2 str6(contri_dia3 contri_dia4 contri_dia5)
13982 "Z090" ""      ""      ""     ""    
13982 "Z223" "U801"  "I702C" "L899" "Z518"
13982 ""     ""      ""      ""     ""    
13982 ""     ""      ""      ""     ""    
14294 ""     ""      ""      ""     ""    
14332 ""     ""      ""      ""     ""    
15454 ""     ""      ""      ""     ""    
15880 "M350" "J459"  "E782"  ""     ""    
15880 "I109" "E785"  ""      ""     ""    
15893 ""     ""      ""      ""     ""    
16223 ""     ""      ""      ""     ""    
17510 ""     ""      ""      ""     ""    
18222 "B028" "E114D" "I501"  ""     ""    
18517 ""     ""      ""      ""     ""    
18517 ""     ""      ""      ""     ""    
18517 "E039" "L940"  ""      ""     ""    
18517 ""     ""      ""      ""     ""    
18517 ""     ""      ""      ""     ""    
18517 ""     ""      ""      ""     ""    
19279 "E869" "N390"  "B962"  "M819" "I109"
19279 "F001" "N178"  "E869"  "E869" "M819"
20180 ""     ""      ""      ""     ""    
20383 ""     ""      ""      ""     ""    
20728 ""     ""      ""      ""     ""    
20759 ""     ""      ""      ""     ""    
21306 "I109" ""      ""      ""     ""    
21306 "T818" "I109"  ""      ""     ""    
21306 "I159" ""      ""      ""     ""    
21306 "I109" "E785"  ""      ""     ""    
21306 "I109" "E785"  ""      ""     ""    
21306 "I109" "E785"  ""      ""     ""    
21306 "I109" "E785"  ""      ""     ""    
21306 "G834" "E119"  "I109"  ""     ""    
21306 "I109" ""      ""      ""     ""    
21306 "Z038" "I109"  "E118"  ""     ""    
21306 "E119" ""      ""      ""     ""    
21306 "I109" "I340"  "E119"  ""     ""    
21306 "I340" "E119"  ""      ""     ""    
21306 "I109" "E119"  ""      ""     ""    
21682 ""     ""      ""      ""     ""    
21682 "I109" "E785"  "I652"  "E119" "G819"
21682 "I109" ""      ""      ""     ""    
21682 ""     ""      ""      ""     ""    
21682 "I714" "Z866A" ""      ""     ""    
21682 "I714" "Z866A" ""      ""     ""    
21952 ""     ""      ""      ""     ""    
22103 ""     ""      ""      ""     ""    
22113 "I209" "E108"  "E669"  ""     ""    
22113 ""     ""      ""      ""     ""    
22113 "I252" "E109"  "I209"  ""     ""    
22113 "E119" "N390X" "I259"  ""     ""    
end

[William Lisowski]

. generate temp_id = _n

. reshape long contri_dia, i(lopnr temp_id) j(from)
(note: j = 1 2 3 4 5)

Data                               wide   ->   long
-----------------------------------------------------------------------------
Number of obs.                       51   ->     255
Number of variables                   7   ->       4
j variable (5 values)                     ->   from
xij variables:
contri_dia1 contri_dia2 ... contri_dia5   ->   contri_dia
-----------------------------------------------------------------------------

. drop if contri_dia==""
(183 observations deleted)

. list if lopnr==13982, abbreviate(12) sepby(temp_id)

     +-------------------------------------+
     | lopnr   temp_id   from   contri_dia |
     |-------------------------------------|
  1. | 13982         1      1         Z090 |
     |-------------------------------------|
  2. | 13982         2      1         Z223 |
  3. | 13982         2      2         U801 |
  4. | 13982         2      3        I702C |
  5. | 13982         2      4         L899 |
  6. | 13982         2      5         Z518 |
     +-------------------------------------+

. drop temp_id from

. list if lopnr==13982, abbreviate(12) clean

       lopnr   contri_dia  
  1.   13982         Z090  
  2.   13982         Z223  
  3.   13982         U801  
  4.   13982        I702C  
  5.   13982         L899  
  6.   13982         Z518  

.

[Zihan Dong]

. generate temp_id = _n

. reshape long contri_dia, i(lopnr temp_id) j(from)
(note: j = 1 2 3 4 5)

Data wide -> long
-----------------------------------------------------------------------------
Number of obs. 51 -> 255
Number of variables 7 -> 4
j variable (5 values) -> from
xij variables:
contri_dia1 contri_dia2 ... contri_dia5 -> contri_dia
-----------------------------------------------------------------------------

. drop if contri_dia==""
(183 observations deleted)

. list if lopnr==13982, abbreviate(12) sepby(temp_id)

+-------------------------------------+
| lopnr temp_id from contri_dia |
|-------------------------------------|
1. | 13982 1 1 Z090 |
|-------------------------------------|
2. | 13982 2 1 Z223 |
3. | 13982 2 2 U801 |
4. | 13982 2 3 I702C |
5. | 13982 2 4 L899 |
6. | 13982 2 5 Z518 |
+-------------------------------------+

. drop temp_id from

. list if lopnr==13982, abbreviate(12) clean

lopnr contri_dia
1. 13982 Z090
2. 13982 Z223
3. 13982 U801
4. 13982 I702C
5. 13982 L899
6. 13982 Z518

.

Thank you! It solved my question!