Hi,

I do have a question regarding a dataset. I try to cumulate my "usd" by month for each country. How to cumulate by month I figured out but I do have an issue to create a new variable that is based on country.

Here my dataset

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input str66 location float(month year usd) double cumusd
"London, England, United Kingdom, Europe"                   1 2010    958540   4638540
"London, England, United Kingdom, Europe"                   1 2010   1580000   4638540
"London, England, United Kingdom, Europe"                   1 2010   2000000   4638540
"Burnaby, British Columbia, Canada, North America"          1 2010    100000   4638540
"London, England, United Kingdom, Europe"                   2 2010   1100000  15973259
"Bagshot, Surrey, United Kingdom, Europe"                   2 2010   3000000  15973259
"Toronto, Ontario, Canada, North America"                   2 2010   1200000  15973259
"Paris, Ile-de-France, France, Europe"                      2 2010    650000  15973259
"Paris, Ile-de-France, France, Europe"                      2 2010  10023259  15973259
"London, England, United Kingdom, Europe"                   3 2010     62001     62001
"London, England, United Kingdom, Europe"                   4 2010   3690000   3690000
"Paris, Ile-de-France, France, Europe"                      5 2010    828835    828835
"London, England, United Kingdom, Europe"                   6 2010  18275602  22025602
"Meylan, Rhone-Alpes, France, Europe"                       6 2010   1350000  22025602
"London, England, United Kingdom, Europe"                   6 2010   2400000  22025602
"London, England, United Kingdom, Europe"                   7 2010  49082060  49083563
"London, England, United Kingdom, Europe"                   7 2010      1503  49083563
"London, England, United Kingdom, Europe"                   8 2010   1736418   1736418
"London, England, United Kingdom, Europe"                   9 2010    700000   2400000
"Paris, Ile-de-France, France, Europe"                      9 2010   1700000   2400000
"London, England, United Kingdom, Europe"                  10 2010   2532150   3332919
"Brighton, East Sussex, United Kingdom, Europe"            10 2010    800769   3332919
"Toronto, Ontario, Canada, North America"                  12 2010   5897461   5897461
"London, England, United Kingdom, Europe"                   1 2011   8300000  14801390
"Vancouver, British Columbia, Canada, North America"        1 2011    501390  14801390
"London, England, United Kingdom, Europe"                   1 2011   6000000  14801390
"London, England, United Kingdom, Europe"                   2 2011   3213610 120510386
"London, England, United Kingdom, Europe"                   2 2011 117296776 120510386
"London, England, United Kingdom, Europe"                   3 2011    965642   1043385
"London, England, United Kingdom, Europe"                   3 2011     77743   1043385
"London, England, United Kingdom, Europe"                   4 2011  2.84e+07  33023423
"London, England, United Kingdom, Europe"                   4 2011    487692  33023423
"London, England, United Kingdom, Europe"                   4 2011   4135731  33023423
"Toronto, Ontario, Canada, North America"                   5 2011   1553160  15425333
"London, England, United Kingdom, Europe"                   5 2011    222521  15425333
"London, England, United Kingdom, Europe"                   5 2011   1000000  15425333
"London, England, United Kingdom, Europe"                   5 2011   5083942  15425333
"Paris, Ile-de-France, France, Europe"                      5 2011   7565710  15425333
"Twickenham, Richmond upon Thames, United Kingdom, Europe"  6 2011    430000   1458890
"Vancouver, British Columbia, Canada, North America"        6 2011   1028890   1458890
"München, Bayern, Germany, Europe"                       7 2011    130000    288698
"London, England, United Kingdom, Europe"                   7 2011     72561    288698
"Boulogne, Nord-Pas-de-Calais, France, Europe"              7 2011     86137    288698
"London, England, United Kingdom, Europe"                   8 2011     71338     71338
"Toronto, Ontario, Canada, North America"                   9 2011   5000000  17607065
"Oakville, Ontario, Canada, North America"                  9 2011  11270434  17607065
"Edinburgh, Edinburgh, City of, United Kingdom, Europe"     9 2011   1336631  17607065
"Paris, Ile-de-France, France, Europe"                     10 2011    127382   5197382
"Toronto, Ontario, Canada, North America"                  10 2011   5000000   5197382
"München, Bayern, Germany, Europe"                      10 2011     70000   5197382
"Paris, Ile-de-France, France, Europe"                     11 2011   5227427   5370406
"Abingdon, Oxfordshire, United Kingdom, Europe"            11 2011    142979   5370406
"München, Bayern, Germany, Europe"                      12 2011    673698  17723058
"Toronto, Ontario, Canada, North America"                  12 2011   9839304  17723058
"London, England, United Kingdom, Europe"                  12 2011    706309  17723058
"Vancouver, British Columbia, Canada, North America"       12 2011    884500  17723058
"München, Bayern, Germany, Europe"                      12 2011    269479  17723058
"Meylan, Rhone-Alpes, France, Europe"                      12 2011   5349768  17723058
"London, England, United Kingdom, Europe"                   1 2012    226154   1173059
"Köln, Nordrhein-Westfalen, Germany, Europe"             1 2012    258905   1173059
"Reading, Reading, United Kingdom, Europe"                  1 2012    688000   1173059
"Paris, Ile-de-France, France, Europe"                      2 2012    530000   5659693
"London, England, United Kingdom, Europe"                   2 2012   2500000   5659693
"Paris, Ile-de-France, France, Europe"                      2 2012    500000   5659693
"Abingdon, Oxfordshire, United Kingdom, Europe"             2 2012    629693   5659693
"London, England, United Kingdom, Europe"                   2 2012   1500000   5659693
"London, England, United Kingdom, Europe"                   3 2012   3304369  30005725
"Paris, Ile-de-France, France, Europe"                      3 2012    640000  30005725
"Vancouver, British Columbia, Canada, North America"        3 2012    100000  30005725
"Vancouver, British Columbia, Canada, North America"        3 2012   1014270  30005725
"Paris, Ile-de-France, France, Europe"                      3 2012   5500000  30005725
"London, England, United Kingdom, Europe"                   3 2012   4000000  30005725
"London, England, United Kingdom, Europe"                   3 2012  1.50e+07  30005725
"Reading, Reading, United Kingdom, Europe"                  3 2012    288000  30005725
"London, England, United Kingdom, Europe"                   3 2012    159086  30005725
"London, England, United Kingdom, Europe"                   4 2012    870933  20670933
"London, England, United Kingdom, Europe"                   4 2012   1300000  20670933
"Edinburgh, Edinburgh, City of, United Kingdom, Europe"     4 2012   2500000  20670933
"London, England, United Kingdom, Europe"                   4 2012  1.60e+07  20670933
"Paris, Ile-de-France, France, Europe"                      5 2012   4265002  27433981
"Berlin, Berlin, Germany, Europe"                           5 2012   5220767  27433981
"London, England, United Kingdom, Europe"                   5 2012   1038977  27433981
"London, England, United Kingdom, Europe"                   5 2012    209235  27433981
"Toronto, Ontario, Canada, North America"                   5 2012  1.20e+07  27433981
"Toronto, Ontario, Canada, North America"                   5 2012   3000000  27433981
"Manchester, Manchester, United Kingdom, Europe"            5 2012   1700000  27433981
"London, England, United Kingdom, Europe"                   6 2012   9000000  21782778
"Berlin, Berlin, Germany, Europe"                           6 2012    124020  21782778
"Frankfurt, Hessen, Germany, Europe"                        6 2012   4000000  21782778
"Cambridge, Cambridgeshire, United Kingdom, Europe"         6 2012   2333519  21782778
"London, England, United Kingdom, Europe"                   6 2012   5325239  21782778
"Hamburg, Hamburg, Germany, Europe"                         6 2012   1000000  21782778
"London, England, United Kingdom, Europe"                   7 2012   1000000  12646629
"Marylebone, Essex, United Kingdom, Europe"                 7 2012     46629  12646629
"London, England, United Kingdom, Europe"                   7 2012   3000000  12646629
"Edmonton, Alberta, Canada, North America"                  7 2012   8600000  12646629
"London, England, United Kingdom, Europe"                   8 2012     18697  54222227
"Köln, Nordrhein-Westfalen, Germany, Europe"             8 2012   1471335  54222227
"London, England, United Kingdom, Europe"                   8 2012   3678337  54222227
"London, England, United Kingdom, Europe"                   8 2012    555341  54222227
end
I try to generate a new variable based on country. So basically I want a new variable that tells me which "usd" is from United Kingdom, which is from Germany, France or Canada.

Code:
gen country = substr(location, -2,3)
I do not know which numbers would work I tried almost everything but I cannot manage to get all four countries

After generating the new variable I want to cummulate all fundings by month and country. For the variable cumusd I used asrol and this code
Code:
bys year month : asrol usd, gen(cumusd) stat(sum) window(month 12)
, so for country I would use something like this
Code:
bys year month country : asrol usd, gen(c_cumusd) stat(sum) window(month 12)
I also have a third question: I would like to use asrol as natural log. but I couldn't find anything that tells me if it is possible. In other words I want to cumulate the "usd" variable as log.

Thanks