Data rearranging

[Daeeun Bae]

Hello. I am working on EUKLEMS and have a quick question.

The data has the following structure.

country db var Sort_ID indnr code year value
AT SDB COMP 1 Agg TOT 1995 90053.0
AT SDB COMP 2 *Agg TOT_IND 1995 89833.2
AT SDB COMP 3 *Agg MARKT 1995 66706.4
AT SDB COMP 4 1 A 1995 363.7
AT SDB COMP 5 2 B 1995 332.1
AT SDB COMP 6 Agg C 1995 20214.4
AT SDB COMP 7 3 C10-C12 1995 2233.0
AT SDB COMP 8 4 C13-C15 1995 1159.4
AT SDB COMP 9 5 C16-C18 1995 2329.6
AT SDB COMP 10 6 C19 1995 244.6
AT SDB COMP 11 7 C20 1995 704.1
AT SDB COMP 12 8 C21 1995 414.3
AT SDB COMP 13 9 C22_C23 1995 2283.2
AT SDB COMP 14 10 C24_C25 1995 3182.2
AT SDB COMP 15 11 C26 1995 1409.3
AT SDB COMP 16 12 C27 1995 1174.8
AT SDB COMP 17 13 C28 1995 2196.9
AT SDB COMP 18 14 C29_C30 1995 930.8
AT SDB COMP 19 15 C31-C33 1995 1952.3
AT SDB COMP 20 16 D 1995 1598.0
AT SDB COMP 21 17 E 1995 717.2
AT SDB COMP 22 18 F 1995 7843.9
AT SDB COMP 23 Agg G 1995 12132.4
AT SDB COMP 24 19 G45 1995 1606.4
AT SDB COMP 25 20 G46 1995 5852.8
AT SDB COMP 26 21 G47 1995 4673.2
AT SDB COMP 27 Agg H 1995 5849.7
AT SDB COMP 28 22 H49 1995 3658.0
AT SDB COMP 29 23 H50 1995 11.6
AT SDB COMP 30 24 H51 1995 279.2
AT SDB COMP 31 25 H52 1995 785.6
AT SDB COMP 32 26 H53 1995 1115.4
AT SDB COMP 33 27 I 1995 3168.3
AT SDB COMP 34 Agg J 1995 2614.1
AT SDB COMP 35 28 J58-J60 1995 777.9
AT SDB COMP 36 29 J61 1995 1162.4
AT SDB COMP 37 30 J62_J63 1995 673.8
AT SDB COMP 38 31 K 1995 5142.6
AT SDB COMP 39 32 L 1995 956.6
AT SDB COMP 40 33 M_N 1995 4245.1
AT SDB COMP 41 Agg O-Q 1995 22170.2
AT SDB COMP 42 34 O 1995 8271.6
AT SDB COMP 43 35 P 1995 7419.7
AT SDB COMP 44 36 Q 1995 6479.0
AT SDB COMP 45 *Agg R_S 1995 2484.9
AT SDB COMP 46 37 R 1995 710.6
AT SDB COMP 47 38 S 1995 1774.3
AT SDB COMP 48 39 T 1995 220.0
AT SDB COMP 991 *Agg C20_C21 1995 1118.4
AT SDB COMP 992 *Agg C26_C27 1995 2584.1
AT SDB COMP 993 *Agg D_E 1995 2315.2
AT SDB EMP 1 Agg TOT 1995 3587.1
AT SDB EMP 2 *Agg TOT_IND 1995 3575.5
AT SDB EMP 3 *Agg MARKT 1995 2785.6
AT SDB EMP 4 1 A 1995 273.1
AT SDB EMP 5 2 B 1995 8.7
AT SDB EMP 6 Agg C 1995 673.3
AT SDB EMP 7 3 C10-C12 1995 91.8
AT SDB EMP 8 4 C13-C15 1995 51.4
AT SDB EMP 9 5 C16-C18 1995 77.5
AT SDB EMP 10 6 C19 1995 3.2
AT SDB EMP 11 7 C20 1995 18.0
AT SDB EMP 12 8 C21 1995 10.9
AT SDB EMP 13 9 C22_C23 1995 70.5
AT SDB EMP 14 10 C24_C25 1995 101.4
AT SDB EMP 15 11 C26 1995 36.0
AT SDB EMP 16 12 C27 1995 37.0
AT SDB EMP 17 13 C28 1995 65.6
AT SDB EMP 18 14 C29_C30 1995 29.5
AT SDB EMP 19 15 C31-C33 1995 80.5
AT SDB EMP 20 16 D 1995 30.1
AT SDB EMP 21 17 E 1995 25.8
AT SDB EMP 22 18 F 1995 280.9
AT SDB EMP 23 Agg G 1995 549.7
AT SDB EMP 24 19 G45 1995 69.3
AT SDB EMP 25 20 G46 1995 192.3
AT SDB EMP 26 21 G47 1995 288.1
AT SDB EMP 27 Agg H 1995 200.1
AT SDB EMP 28 22 H49 1995 135.1
AT SDB EMP 29 23 H50 1995 0.7
AT SDB EMP 30 24 H51 1995 6.5
AT SDB EMP 31 25 H52 1995 20.5
AT SDB EMP 32 26 H53 1995 37.4
AT SDB EMP 33 27 I 1995 211.7
AT SDB EMP 34 Agg J 1995 68.1
AT SDB EMP 35 28 J58-J60 1995 25.8
AT SDB EMP 36 29 J61 1995 24.1
AT SDB EMP 37 30 J62_J63 1995 18.2
AT SDB EMP 38 31 K 1995 125.4
AT SDB EMP 39 32 L 1995 48.6
AT SDB EMP 40 33 M_N 1995 213.2
AT SDB EMP 41 Agg O-Q 1995 741.3
AT SDB EMP 42 34 O 1995 260.7
AT SDB EMP 43 35 P 1995 204.7
AT SDB EMP 44 36 Q 1995 275.9
AT SDB EMP 45 *Agg R_S 1995 125.4
AT SDB EMP 46 37 R 1995 35.1
AT SDB EMP 47 38 S 1995 90.3
AT SDB EMP 48 39 T 1995 11.6
AT SDB EMP 991 *Agg C20_C21 1995 28.9
AT SDB EMP 992 *Agg C26_C27 1995 73.1

So, I would like to make variables EMP and EMPE for each country instead of the variable value. For example, I would like to make a list of variables as follows.

country db (var) Sort_ID indnr code year COMP EMP (value)

Here (var) and (value) should be removed.

Therefore, the number of rows will decrease and the number of columns will increase.

I was wondering how I could make those variables.

Thank you.

[Daniel Schaefer]

Hello Daeeun,

I am afraid I don't understand what you are asking. Can you be a little bit more specific? Are you asking to generate new variables, and if so, what should those variables look like? Or do you want to replace the values in the variable var with the country when the value equals EMP or EMPE?

Thanks for the data example. You can turn this into a runnable command so that your readers can test code locally before posting it here with -dataex-.

clear
input str10(country db var) int(Sort_ID) str10(indnr code) float(year value)
AT SDB COMP 1 Agg TOT 1995 90053.0
AT SDB COMP 2 *Agg TOT_IND 1995 89833.2
AT SDB COMP 3 *Agg MARKT 1995 66706.4
AT SDB COMP 4 1 A 1995 363.7
AT SDB COMP 5 2 B 1995 332.1
AT SDB COMP 6 Agg C 1995 20214.4
AT SDB COMP 7 3 C10-C12 1995 2233.0
AT SDB COMP 8 4 C13-C15 1995 1159.4
AT SDB COMP 9 5 C16-C18 1995 2329.6
AT SDB COMP 10 6 C19 1995 244.6
AT SDB COMP 11 7 C20 1995 704.1
AT SDB COMP 12 8 C21 1995 414.3
AT SDB COMP 13 9 C22_C23 1995 2283.2
AT SDB COMP 14 10 C24_C25 1995 3182.2
AT SDB COMP 15 11 C26 1995 1409.3
AT SDB COMP 16 12 C27 1995 1174.8
AT SDB COMP 17 13 C28 1995 2196.9
AT SDB COMP 18 14 C29_C30 1995 930.8
AT SDB COMP 19 15 C31-C33 1995 1952.3
AT SDB COMP 20 16 D 1995 1598.0
AT SDB COMP 21 17 E 1995 717.2
AT SDB COMP 22 18 F 1995 7843.9
AT SDB COMP 23 Agg G 1995 12132.4
AT SDB COMP 24 19 G45 1995 1606.4
AT SDB COMP 25 20 G46 1995 5852.8
AT SDB COMP 26 21 G47 1995 4673.2
AT SDB COMP 27 Agg H 1995 5849.7
AT SDB COMP 28 22 H49 1995 3658.0
AT SDB COMP 29 23 H50 1995 11.6
AT SDB COMP 30 24 H51 1995 279.2
AT SDB COMP 31 25 H52 1995 785.6
AT SDB COMP 32 26 H53 1995 1115.4
AT SDB COMP 33 27 I 1995 3168.3
AT SDB COMP 34 Agg J 1995 2614.1
AT SDB COMP 35 28 J58-J60 1995 777.9
AT SDB COMP 36 29 J61 1995 1162.4
AT SDB COMP 37 30 J62_J63 1995 673.8
AT SDB COMP 38 31 K 1995 5142.6
AT SDB COMP 39 32 L 1995 956.6
AT SDB COMP 40 33 M_N 1995 4245.1
AT SDB COMP 41 Agg O-Q 1995 22170.2
AT SDB COMP 42 34 O 1995 8271.6
AT SDB COMP 43 35 P 1995 7419.7
AT SDB COMP 44 36 Q 1995 6479.0
AT SDB COMP 45 *Agg R_S 1995 2484.9
AT SDB COMP 46 37 R 1995 710.6
AT SDB COMP 47 38 S 1995 1774.3
AT SDB COMP 48 39 T 1995 220.0
AT SDB COMP 991 *Agg C20_C21 1995 1118.4
AT SDB COMP 992 *Agg C26_C27 1995 2584.1
AT SDB COMP 993 *Agg D_E 1995 2315.2
AT SDB EMP 1 Agg TOT 1995 3587.1
AT SDB EMP 2 *Agg TOT_IND 1995 3575.5
AT SDB EMP 3 *Agg MARKT 1995 2785.6
AT SDB EMP 4 1 A 1995 273.1
AT SDB EMP 5 2 B 1995 8.7
AT SDB EMP 6 Agg C 1995 673.3
AT SDB EMP 7 3 C10-C12 1995 91.8
AT SDB EMP 8 4 C13-C15 1995 51.4
AT SDB EMP 9 5 C16-C18 1995 77.5
AT SDB EMP 10 6 C19 1995 3.2
AT SDB EMP 11 7 C20 1995 18.0
AT SDB EMP 12 8 C21 1995 10.9
AT SDB EMP 13 9 C22_C23 1995 70.5
AT SDB EMP 14 10 C24_C25 1995 101.4
AT SDB EMP 15 11 C26 1995 36.0
AT SDB EMP 16 12 C27 1995 37.0
AT SDB EMP 17 13 C28 1995 65.6
AT SDB EMP 18 14 C29_C30 1995 29.5
AT SDB EMP 19 15 C31-C33 1995 80.5
AT SDB EMP 20 16 D 1995 30.1
AT SDB EMP 21 17 E 1995 25.8
AT SDB EMP 22 18 F 1995 280.9
AT SDB EMP 23 Agg G 1995 549.7
AT SDB EMP 24 19 G45 1995 69.3
AT SDB EMP 25 20 G46 1995 192.3
AT SDB EMP 26 21 G47 1995 288.1
AT SDB EMP 27 Agg H 1995 200.1
AT SDB EMP 28 22 H49 1995 135.1
AT SDB EMP 29 23 H50 1995 0.7
AT SDB EMP 30 24 H51 1995 6.5
AT SDB EMP 31 25 H52 1995 20.5
AT SDB EMP 32 26 H53 1995 37.4
AT SDB EMP 33 27 I 1995 211.7
AT SDB EMP 34 Agg J 1995 68.1
AT SDB EMP 35 28 J58-J60 1995 25.8
AT SDB EMP 36 29 J61 1995 24.1
AT SDB EMP 37 30 J62_J63 1995 18.2
AT SDB EMP 38 31 K 1995 125.4
AT SDB EMP 39 32 L 1995 48.6
AT SDB EMP 40 33 M_N 1995 213.2
AT SDB EMP 41 Agg O-Q 1995 741.3
AT SDB EMP 42 34 O 1995 260.7
AT SDB EMP 43 35 P 1995 204.7
AT SDB EMP 44 36 Q 1995 275.9
AT SDB EMP 45 *Agg R_S 1995 125.4
AT SDB EMP 46 37 R 1995 35.1
AT SDB EMP 47 38 S 1995 90.3
AT SDB EMP 48 39 T 1995 11.6
AT SDB EMP 991 *Agg C20_C21 1995 28.9
AT SDB EMP 992 *Agg C26_C27 1995 73.1
end

[Daeeun Bae]

Hi Daniel. I just edited the question. Thank you for your response.

[Jenna Grabey]

From what you have written I am not sure what the EMP and EMPE variables are and how you would calcualte these by country. To generate these you would code somethin like the following:

gen EMP = [insert condition of new variable]
gen EMPE = [insert condition of new variable]

Then to remove variables you can code as follows:

drop var
drop value

In what I've said here your number of rows will remain the same. If you can provide more details on how you would calcualte EMP I'm happy to try and help more.

[William Lisowski]

I think you are describing the results of

reshape long

Perhaps this example code using the data formatted helpfully in post #2 will start you in a useful direction.

. list if Sort_ID<=5, clean noobs

    country    db    var   Sort_ID   indnr   code   year    value  
         AT   SDB   COMP         1     Agg    TOT   1995    90053  
         AT   SDB   COMP         4       1      A   1995    363.7  
         AT   SDB   COMP         5       2      B   1995    332.1  
         AT   SDB    EMP         1     Agg    TOT   1995   3587.1  
         AT   SDB    EMP         4       1      A   1995    273.1  
         AT   SDB    EMP         5       2      B   1995      8.7  

. reshape wide value, i(country db Sort_ID indnr code year) j(var) string
(j = COMP EMP)

Data                               Long   ->   Wide
-----------------------------------------------------------------------------
Number of observations               90   ->   45          
Number of variables                   8   ->   8           
j variable (2 values)               var   ->   (dropped)
xij variables:
                                  value   ->   valueCOMP valueEMP
-----------------------------------------------------------------------------

. rename value* *

. list if Sort_ID<=5, clean noobs

    country    db   Sort_ID   indnr   code   year    COMP      EMP  
         AT   SDB         1     Agg    TOT   1995   90053   3587.1  
         AT   SDB         4       1      A   1995   363.7    273.1  
         AT   SDB         5       2      B   1995   332.1      8.7  

.