Creating variables from a tabulate command

[Rob Winter]

Hi all,


I have a dataset that is in long format, whereby each observation describes an individual project, including which country this project was in. This means that if there was more than 1 project in a country, there a multiple observations for that country.

I am interested in how many projects there are, by country. From here, I want to create new variables that describe this.

I understand that I may be explaining this poorly - I hope my example and code makes what I am trying to achieve clearer.

Initially, I can partially achieve what I am after by running:

tab recip2 intent2

Which provides a table of what type of projects and total number of projects, by country (recip2)

I have tried to create variables with the data by running the following:

tab recip2 intent2, matcell(x)
matrix list x
svmat x

However, when I run this, it doesn't include the country variable (recip2) as an ID variable, like it shows in the initial table using the tab command.
It creates the variables x1, x2, x3, x4, x5 with the correct values for project intention (intent2)

Is there a way I can include the country as a variable?
As for total number of projects, I imagine I can create this afterwards by using an egen rowtotal command loop (after dropping all variables other than the ones created in this process)?


I hope this makes sense. Apologies if it does not and I will try my best to clarify. Below is some example code which I hope may make this more sensical!

Any help is greatly appreciated as always.

clear
input long AidDataTUFFProjectID int(CommitmentYear ImplementationStartYear CompletionYear) double AmountConstantUSD2017 long(recip2 status2 sectorname2 flowclass2 flowtype2 intent2) float(x1 x2 x3 x4 x5)
  4 2001 2004 2006 13095371.5173268   6 2 14 2 6 2  0  92 10   1  3
  6 2001 2001 2001 1309537.15173268   6 2  9 2 5 2  0  33  3   4  1
 10 2000    .    .                .   6 4 13 2 7 2  0  25  1   0 12
 13 2001    .    .                .   6 2 13 2 5 2  0  30  0   3  1
 14 2003 2003 2003 1277285.29905012   4 2  9 2 5 2  0   5  0   1  1
 16 2009 2009    .                .  58 3  2 2 7 2  5 128  0 184  1
 18 2000    . 2002 19777599.6089836  16 2 15 3 6 4  0  46  2   0  2
 20 2003    .    .  42288864.491973   4 2  5 3 3 4 20   4  0  26  4
 23 2001 2009 2012 21094817.8285375  19 2 18 2 6 2  0  21  7   0  8
 24 2007    .    .                .  16 2  1 2 1 2  0  28  0   0  3
 29 2005 2004    .                .  19 2 14 2 4 2  1   9  2   4  4
 31 2006 2006 2008 14089831.8656307  19 2 18 2 6 2  0  25  1   3  1
 32 2005    .    .                .  16 2  8 2 7 2  0  61  0  19  9
 33 2006    .    .  211611.99297568  19 2  8 2 5 2  0  42  5   7  8
 34 2006    .    .                .  16 3 13 2 5 2  1  40  6  29 12
 38 2007 2008 2009 2052840.82185981  19 2  8 2 5 2  0  84  4   6  1
 39 2009    .    .                .  19 2  2 2 4 2  1  50 20   8  5
 40 2009 2010 2014 1075628194.29734  19 2 10 3 3 4  0  12  3   3  2
 42 2009    .    . 3646.35293709898  19 2  8 2 5 2  0  67  0   2  4
 46 2009 2009 2009                .  19 2  8 3 5 5 27  11  0  61 11
 47 2002 2002 2002  1952518.6090752   6 2 13 2 5 2  1  13  0  10  2
 48 2002 2002 2002 1310535.30508861   6 2  9 2 5 2  1  17  0   2 15
 49 2002    .    . 650839.536358402   6 2  9 2 5 2  0  83  3   1  2
 51 2002    .    .                .   6 2 13 2 5 2  0  65  6   2  2
 53 2007 2006 2009  23809691.980076 120 2  5 2 6 2  3 171 15  70 17
 57 2004    .    .                .   6 3  5 2 7 2  1  91  4  13  3
 59 2005 2005 2005 1884110.49736411   6 2  2 2 5 2  0  84  3   1  0
 63 2005    .    . 71574726.4818916   6 2  5 4 6 2  0  78  0   3  2
 65 2007    .    . 40470114.7255072   6 3 24 3 3 4  3  10  0   7  7
 66 2007 2005 2008 79169209.7239682   6 2 22 3 3 4  0  22  4   2  5
 69 2010 2010 2010  53565534.324448   6 2 18 2 5 2  0  60  2   0  2
 74 2005    . 2005 2655799.06156492  81 2  5 2 5 2  0 138  2   7  2
 80 2003    . 2003 7496231.49183316  48 2 22 2 5 2  0  19  0   0  2
 83 2002 2003 2005 17385561.3573055  99 2 24 2 5 2  1  22  0   4  6
 85 2008 2008 2014 92649900.7229842  51 2 10 2 6 2  0  62  4   5  1
 87 2003    . 2013 12686659.3475919  46 2 22 2 6 2  1  76  0  18  4
 91 2004 2005 2006 15637760.3556125  23 2  5 3 3 4  0   1  0   0  1
 96 2004 2004 2007 19769608.5405974 126 2 22 2 5 2  0 109  0   4  2
 99 2001 2001 2003 26938082.3670424  46 2 22 2 6 2  0 148  6  55  2
100 2006 2007 2009 22119295.3049387  48 2 22 2 6 2  1  76  2   4  0
105 2006    . 2010 33178942.9574081  19 2 22 2 6 2  0  66  0   3  2
109 2007 2006 2010 28049575.6291158  19 2 22 2 6 2  0   2  0   0  1
115 2001 2001 2004 57193247.4477634 130 2 24 2 5 2  5  66  5  14  1
117 2008 2008    . 30470195.6507751  16 2 22 2 5 2  2  39  2   7  4
119 2003 2003 2005 51091411.9620048  19 2 22 2 6 2  0   1  0   0  0
120 2003 2004 2006 58755123.7563055  54 2 22 2 6 2  1  76  1  21  2
131 2005 2007 2008 57469555.0838921  95 2 22 2 6 2  0  48  0   6  3
134 2008 2009 2010 46657487.0902493  39 2  5 2 6 2  1 138  1  28  7
135 2006 2008 2011 50431993.2952603  99 2 22 2 9 2  0 111  6   1 12
136 2006    .    . 76709347.4536843   6 4  5 3 6 4  1   0  0   0  0
138 2007 2007 2016 75955110.4088131 117 2 10 2 6 2  0  55  2   7  0
141 2011 2011 2014 76896671.6247575  32 2 22 3 3 4  0  12  0   1  1
147 2005    .    . 119460798.289351  27 2  5 3 8 4  0  25  0   5  6
150 2006    . 2009 142838095.258584 126 2 10 3 8 4  2  93 18  17  6
151 2006 2007    . 142838095.258584  54 2 10 3 8 4  0  76  0   1  5
152 2002 2003 2009  195251860.90752   6 2 22 3 8 4  0   1  0   0  0
158 2001 2003 2004 284780040.685256 126 2 10 3 8 4  0  70  2   5  2
161 2007 2007 2011 126476243.202136  32 2 22 3 3 4  0  69  3   0  0
165 2005    .    . 376709086.746797 126 4  5 4 6 2  1  56  6   2  3
178 2003    . 2010 1285581480.55598 126 2 10 2 6 2  0  30  0   0  1
183 2007 2008 2016 455939049.568194  54 2 10 3 3 4  0   3  0   0  0
195 2010 2011 2016 604316577.717511 100 2 22 3 3 4  2   7  1  15  7
201 2009 2009 2011 67858366.4868061  26 2  5 3 3 4 27  97  7  89 11
205 2009    . 2011                .  46 2 10 3 3 4  0  23  0  32  4
206 2002    .    .                .   6 2  5 4 6 2  0  14  2   1  0
207 2007 2013 2014 1714662912.39374 126 2 22 3 3 4  4   1  0   1  6
208 2006    .    . 50963221.6416431 126 3 24 2 6 2  1  50  3   7 11
230 2007    .    . 7046826.46655975 130 2 24 2 5 2  0  46  5   4  9
231 2007 2007 2009                .  51 2 22 2 5 2 16  47  5  26  8
233 2007    . 2007                . 133 2 10 4 9 2  1 127  5  18  9
234 2007 2009 2011  71007764.028131  81 2 22 2 5 2  2  56 15  12  8
237 2002 2004 2006 8166998.40081115  24 2 24 2 5 2  5 146  4  48  4
239 2004 2004 2005                . 113 2 22 2 5 2  0  60  6   0  3
243 2006 2006 2008 14107466.1983787 148 2  5 3 6 1  0  93  4   1  2
246 2004 2007 2009 23723530.2487169 126 2 22 3 8 4  0 117 12   3  2
248 2005    .    . 61215226.5963546 126 3 24 3 6 2  1   5  0   0  0
256 2011    . 2010 107386459.763923  99 2 15 3 6 1  1  92  2   2  5
277 2001 2001 2001 100933536.379289  23 2  1 2 1 2  1 135  2   0  5
278 2002    .    .                .  26 2  1 4 2 2  6  14  0   5  3
284 2003    .    . 12686659.3475919  26 2 14 2 5 2  0  55  0   7  3
285 2007 2009    .                .  51 2  2 2 4 2  0 105  8   2  3
286 2007 2009 2009                .  51 2 13 1 7 3 30   0  0   0  0
287 2003    .    . 331144.132070496  26 2 14 2 5 2  0  76  3   1  3
288 2002 2002 2002                .  23 2  8 2 5 2  1  63  0   2  2
289 2011 2012 2016 578397559.017688  26 2 10 3 3 4  1  18  0   5 24
295 2005    .    .                .  26 3  2 2 4 2  0 104  0   2  9
296 2006    . 2007 1618880.71740566  26 2  8 2 5 2  0   3  0   0  0
299 2006 2006 2006                .  23 2 13 1 5 3  0  26  8   0  3
301 2010    .    .                .  19 2  8 2 7 2  3  78  5  25 15
311 2007    . 2007 50042637.9057477  26 2  1 2 1 2  0  13  3   3  1
314 2009    . 2015 9541930.41161445  26 2 14 2 6 2  0  36  0   4  5
315 2007 2008 2010 35103578.0538028  26 2 24 2 6 2  2  79 12  11  3
316 2007 2007 2007  43725509.505614  23 2  1 2 1 2  0  23  0   1  1
317 2007    . 2007                .  26 2  5 2 5 2  1 172  1  54 14
319 2007    . 2008 686736.333320675  23 2 14 2 5 2  2  91  6   7  4
321 2007    .    . 91223.5586823527  26 5 13 1 5 3  0   8  0   0  0
326 2000    .    .                .  32 4 13 1 5 3  0 108  7   3  7
334 2001    . 2001 93850162.5408425  32 2  1 2 1 2  0   1  0   0  0
337 2000    . 2000                .  19 2  9 2 5 2  2  90  1   1  0
338 2010 2010    .                .  26 3 22 2 5 2  1  39  3  21  5
end
label values recip2 recip2
label def recip2 4 "Algeria", modify
label def recip2 6 "Angola", modify
label def recip2 16 "Benin", modify
label def recip2 19 "Botswana", modify
label def recip2 23 "Burundi", modify
label def recip2 24 "Cabo Verde", modify
label def recip2 26 "Cameroon", modify
label def recip2 27 "Central African Republic", modify
label def recip2 32 "Congo", modify
label def recip2 39 "Democratic Republic of the Congo", modify
label def recip2 46 "Equatorial Guinea", modify
label def recip2 48 "Ethiopia", modify
label def recip2 51 "Gabon", modify
label def recip2 54 "Ghana", modify
label def recip2 58 "Guinea-Bissau", modify
label def recip2 81 "Mali", modify
label def recip2 95 "Namibia", modify
label def recip2 99 "Niger", modify
label def recip2 100 "Nigeria", modify
label def recip2 113 "Rwanda", modify
label def recip2 117 "Senegal", modify
label def recip2 120 "Sierra Leone", modify
label def recip2 126 "Sudan", modify
label def recip2 130 "Tanzania", modify
label def recip2 133 "Togo", modify
label def recip2 148 "Zambia", modify
label values status2 status2
label def status2 2 "Completion", modify
label def status2 3 "Implementation", modify
label def status2 4 "Pipeline: Commitment", modify
label def status2 5 "Pipeline: Pledge", modify
label values sectorname2 sectorname2
label def sectorname2 1 "ACTION RELATING TO DEBT", modify
label def sectorname2 2 "AGRICULTURE, FORESTRY, FISHING", modify
label def sectorname2 5 "COMMUNICATIONS", modify
label def sectorname2 8 "EDUCATION", modify
label def sectorname2 9 "EMERGENCY RESPONSE", modify
label def sectorname2 10 "ENERGY", modify
label def sectorname2 13 "GOVERNMENT AND CIVIL SOCIETY", modify
label def sectorname2 14 "HEALTH", modify
label def sectorname2 15 "INDUSTRY, MINING, CONSTRUCTION", modify
label def sectorname2 18 "OTHER SOCIAL INFRASTRUCTURE AND SERVICES", modify
label def sectorname2 22 "TRANSPORT AND STORAGE", modify
label def sectorname2 24 "WATER SUPPLY AND SANITATION", modify
label values flowclass2 flowclass2
label def flowclass2 1 "Military", modify
label def flowclass2 2 "ODA-like", modify
label def flowclass2 3 "OOF-like", modify
label def flowclass2 4 "Vague (Official Finance)", modify
label values flowtype2 flowtype2
label def flowtype2 1 "Debt forgiveness", modify
label def flowtype2 2 "Debt rescheduling", modify
label def flowtype2 3 "Export Buyer's Credit", modify
label def flowtype2 4 "Free-standing technical assistance", modify
label def flowtype2 5 "Grant", modify
label def flowtype2 6 "Loan", modify
label def flowtype2 7 "Scholarships/training in the donor country", modify
label def flowtype2 8 "Supplier's Credit/Export Seller's Credit", modify
label def flowtype2 9 "Vague TBD", modify
label values intent2 intent2
label def intent2 1 "Commercial", modify
label def intent2 2 "Development", modify
label def intent2 3 "Military", modify
label def intent2 4 "Mixed", modify
label def intent2 5 "Representational", modify

[Carlo Lazzaro]

Rob:
if you want the total number of projects for each country, you may want to try:

. bysort recip2: egen wanted=count(_n)

If you want the progressive number of projects for each country, youmay want to try instead:

bysort recip2: gen counter=_n

[Rob Winter]

Hi Carlo,

Many thanks for your response.

I have realised through using your code that I am, overall, actually looking to do something much more complex. This is entirely my fault - I have been getting myself confused by playing around with this dataset for so long! I will try and edit my main post to reflect the below:

I suppose what I am actually trying to do is transform the entire dataset so that the country is the ID variable, which I believe is transforming it from long to wide? The number of total projects and the breakdown of the different intent of projects would form one part of this and I (likely incorrectly) assumed that from here, I could use similar code to include the number of different values of sectorname2, status2, flowclass2, flowtype2 by country?

For example, an observation could be identified as Angola, and I could have variables created from intent2 for No. of Commerical projects, No. of Military projects.. and so on. Then for status2, this to be made so variables could be No. of Completed projects, No. of implemented projects.. and so on.

I understand then for this dataset to make sense, I would have to drop variables such as CommitmentYear, CompletitionYear and ImplementationStartYear.

I hope this makes sense.

Any advice is greatly appreciated!

[Nick Cox]

local vars 

foreach x in  sectorname2 status2 flowclass2 flowtype2 { 
    separate `x', by(`x') veryshortlabel 
    local vars `vars' `r(varlist)'
    quietly foreach v in `r(varlist)' { 
        replace `v' = !missing(`v')
    }
    drop `x' 
}

collapse (sum) `vars', by(recip2)

[Rob Winter]

Thank you Nick, very much appreciated!