How to save the value of a local in another dataset

[Nicolo Framarin]

Hi all

I'd like to store the results of my .do file in a new dataset.

For example, I have four different datasets named IT_2018, IT_2019, UK_2018 and UK_2019.
I calculated in a unique .do file the number of individuals, children, working age, elderly for each dataset and I obtained these locals: IT_2018_i_no ; IT_2018_k_no ; IT_2018_w_no and so on and so forth.

I created a different dataset in which I'd like to store all of these results. The datasets will have 6 variables:
- country
- year
- i_no (number of individual)
- k_no (number of children)
- w_no (number of working age people)
- e_no (number of elderly)
and 4 observations:
- IT 2018
- IT 2019
- UK 2018
- UK 2019

How can i say to Stata: "take each local, see its name and fill the corresponding cell in the new dataset?".
At the end of the day, I'd like to have a "results dataset" like this:

country years i_no k_no w_no e_no
IT 2018 564 45 ... 136
IT 2019 573 51 ... 127
and so on and so forth.

[Maarten Buis]

see help post

[Nicolo Framarin]

Ok, I have read the help window, but I didn't undestand how to use this command...
I mean, the syntax is:

postfile postname newvarlist using filename
-> postname in the name of the variable in which I should save the result in my "results dataset"
-> "newvarlist" seems not be useful for my purposes
-> filename is the nema of my "results dataset"

It doesn't allow me to specify in which observation of the new dataset I want to store the value.

[William Lisowski]

Perhaps clicking on the link at the top of the help post that opens the full documentation in the PDF and reviewing the complete example shown there will help your understanding.

The postfile command creates a new dataset, it does not write into an existing empty dataset. It adds one observation at a time. The newvarlist is how you tell postfile what the variable names are to be called in the dataset it creates. The post command adds a single observation to the new dataset.

[Nicolo Framarin]

Ok, thank you both.

The fact is that I already have a dataset, in which I have all of the variables I need. The cells corresponding to "country" and "year" variables are already filled, while the cells of the other variables are not - in these cells I have to write the values I obtain during my .do file. The name of the variables other than "country" and "year" is equal to the final part of locals' name (e.g. local IT_2016_k_bfa in the "results dataset" I have a variable named k_bfa, while the first part of the local's name is given by the two variables "country" and "year" - we could think at these two variables as a sort of identifiers).

Actually, I have 28 country, 13 years and 13 scenarios per year - something like 5000 datasets. My purpose is to store in a unique dataset the 10-20 results that I produce for each of those 5000 datasets... And use those results for creating graphs, tables, and so on...

[William Lisowski]

Your description remains unclear.

Have your do-file use postfile to create a new dataset with variables identifying the country, year, and scenario, and variables containing each of the 10-20 values that your do-file calculates for that country/year/scenario.

When you're done, if you like, merge the new dataset the dataset you already have.

[Nicolo Framarin]

Ok, if I correctly understood you mean that it doesn't matter if I already have a dataset, since all of I need is to know how to use postfile - is it?
I still have a doubt: how can I say to Stata, using the command "post", in which cell insert a certain value?

Thank you

[William Lisowski]

. sysuse auto, clear
(1978 Automobile Data)

. postfile summary Mean Std_Dev using myresults, replace

. summarize price

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
       price |         74    6165.257    2949.496       3291      15906

. return list

scalars:
                  r(N) =  74
              r(sum_w) =  74
               r(mean) =  6165.256756756757
                r(Var) =  8699525.974268788
                 r(sd) =  2949.495884768919
                r(min) =  3291
                r(max) =  15906
                r(sum) =  456229

. post summary (`r(mean)') (`r(sd)')

. summarize weight

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
      weight |         74    3019.459    777.1936       1760       4840

. post summary (`r(mean)') (`r(sd)')

. postclose summary

. use myresults, clear

. describe

Contains data from myresults.dta
  obs:             2                          
 vars:             2                          24 Feb 2020 16:18
------------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
------------------------------------------------------------------------------------------------
Mean            float   %9.0g                 
Std_Dev         float   %9.0g                 
------------------------------------------------------------------------------------------------
Sorted by: 

. list

     +---------------------+
     |     Mean    Std_Dev |
     |---------------------|
  1. | 6165.257   2949.496 |
  2. | 3019.459   777.1935 |
     +---------------------+

.

[Nicolo Framarin]

Hi

Does the command work only with numeric values?
Because I also have non numeric variables (like country).

I have the EU28 countries, 13 years (from 2007 to 2019) and 13 scenarios (from v1 to v13). For each country, year and scenario I have a dataset (e.g. IT_2007_v1.dta, IT_2007_v2.dta etc.).
I wrote this command:

postfile EU28_stats cnt yrs meany pline using EU28_stats, replace

where meany is mean income, and pline is poverty line (I have other 150 variables).
Now, to do the same processo for all countries, years and scenarios i used the following loop:

global cnt28 = "AT BE BG CY CZ ... UK"
global years = "2007 2008 2009 ... 2010"
global scen = "v1 v2 v3 ... v13"

foreach cnt of global cnt28 {
foreach yrs of global years {
foreach scn of global scen {
post EU28_stats (`cnt') (`yrs') (`scn') (`meany') (`pline')
}
}
}
postclose EU28_stats

It gives me error r(111) saying "AT not found". Therefore, I think it does not works with non numeric variables...

[William Lisowski]

The command works with strings, your code has a number of errors.

The documentation explains that on the post command, the contents of each set of parentheses are an expression. The expression

AT

is interpreted as the value of the variable AT. What you want is the expression

"AT"

which is a character string constant. So you need quotation marks around the first and third expressions so that Stata treats them as character string constants, and you need to create the first and third variables in the postfile as string variables to receive these string values. (Where by "third variable" I mean the one you forgot between yrs and meany.)

postfile EU28_stats str2 cnt yrs str3 scn meany pline using EU28_stats, replace

lobal cnt28 = "AT BE BG CY CZ ... UK"
global years = "2007 2008 2009 ... 2010"
global scen = "v1 v2 v3 ... v13"

foreach cnt of global cnt28 {
foreach yrs of global years {
foreach scn of global scen {
post EU28_stats ("`cnt'") (`yrs') ("`scn'") (`meany') (`pline')
}
}
}
postclose EU28_stats

[Nicolo Framarin]

Hi, thank you again!
I have another problem with the command... Let's not consider the version global for the moment. The following is the code corrected following your suggestions:

************************************************** ***************
postfile EU28_stats str2 cnt yrs meany

global cnt28 "AT BE BG CY CZ ... UK"
global years "2007 2008 209 ... 2019"

foreach cnt of global cnt28 {
foreach yrs of global years {
post EU28_stats ("`cnt'") (`yrs') (``cnt'_`yrs'_meany')
}
}

postclose EU28_stats
************************************************** ***************

As you can see, I name the local containing the mean income of each dataset ad: `cnt'_`yrs' _meany (in such a way, I immediately know which country and year it refers to).
When I run the codo, I get this error:

invalid syntax
post: above message corresponds to expression 3, variable meany
r(198);

`cnt'_`yrs' _meany is a local (actually, a number of locals), therefore I used `.' to address it. For example, for AT_2007 it is 1,500...

Thank you

(this command is more difficult than it was expected)

[Nick Cox]

Probably this is the wrong point at which to say that your whole approach is a little odd to me: I would just append the four datasets at the outset, from what I understand of your problem.

That said, where is the local macro `cnt'_`yrs'_meany ?

Your approach will work if and only if the local macro name is in the namespace visible to your code. That's what local means.

[Nicolo Framarin]

Hi Nick

My .do file is not very long (around 350 raws), but uses a large number of datasets (it generates something like 150 statistics for each country for each year).
It is made up of three loops: the first one just uniforms notations between the different datasets; the second one (in turn composed of three loops) generates the statistics I need; the third one is the one above, with which I fill all cells in the EU28_stats dataset. The .do file is like:

global cnt28 "AT BE BG CY CZ ... UK"
global years "2007 2008 209 ... 2019"

foreach cnt of global cnt28 {
foreach yrs of global years {
insheet using ""
... here I uniformed notations ...
save ""
clear
}
}

foreach cnt of global cnt28 {
foreach yrs of global years {
use ""
... here I perfomed statistics (among which `cnt'_`yrs'_meany ...
save "", replace
clear
}
}

postfile EU28_stats str2 cnt yrs meany
foreach cnt of global cnt28 {
foreach yrs of global years {
post EU28_stats ("`cnt'") (`yrs') (``cnt'_`yrs'_meany')
}
}

[William Lisowski]

Nicolo Framarin -

For me to continue to try to assist you, I am going to have to have better structure in the discussion. You should return to post #10.

1. Run your do-file using the corrections shown in post #10. To reduce the size of the problem, you should reduce cnt28 to just AT and BE; years to just 2007 and 2008; scn to just v1 and v2. Do not make any simplifications to the code otherwise, doing so increases the opportunity for error either in the simplification or in undoing the simplification later.
2. If the do-file ends with an error
   1. start a new post
   2. in your Results window copy the output starting with the code before the error message and including the entire the error message and paste it into the post using CODE delimiters
   3. in your Do-file Editor window copy the entire contents and paste it into the post using CODE delimiters
   4. we will work to get the do-file to run with no errors
      1. In particular it appears you are creating and hundreds of local variables for later output with postfile; it should be possible to integrate postfile into the process that creates the local variables rather than running it as a separate set of loops
3. When the do-file runs with no error
   1. start a new post
   2. in your Do-file Editor window copy the entire contents and paste it into the post using CODE delimiters
   3. use the file you created with the postfile command
   4. use the dataex command to prepare a sample of the data for presentation in a post
   5. in your Results window copy the output from dataex starting with the line containing [CODE] and ending with the line containing [/CODE] and paste it into the post
   6. you apparently want a different dataset organization, so at this point explain what you want, illustrating with examples that use the data shown in the dataex output
   7. we will tell you how to transform the dataset you have into the dataset you want, rather than complicating the postfile and post commands as you have attempted in post #11

But before you start, please prepare yourself to improve the presentation of your problems in Statalist.

Read the FAQ

Take a few moments to review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question. It's particularly helpful to copy commands and output from your Stata Results window and paste them into your Statalist post using code delimiters [CODE] and [/CODE], and to use the dataex command to provide sample data, as described in section 12 of the FAQ.

Using CODE delimiters

To present code and results readably, copy them from the Results window or elsewhere and paste them into a code block in the Forum editor, as explained in the Statalist FAQ linked to at the top of the page. For example, the following text

[CODE]
// sample code
sysuse auto, clear
describe
[/CODE]

will be presented in the post as the following:

// sample code
sysuse auto, clear
describe

Using dataex to present example data

If you are running version 15.1 or later, or a fully updated version 14.2, dataex is already part of your official Stata installation. If not, run ssc install dataex to get it. Either way, run help dataex and read the simple instructions for using it. dataex includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

. sysuse auto, clear
(1978 Automobile Data)

. dataex make price foreign in 51/60

----------------------- copy starting from the next line -----------------------  
[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input str18 make int price byte foreign
"Pont. Phoenix" 4424 0
"Pont. Sunbird" 4172 0
"Audi 5000"     9690 1
"Audi Fox"      6295 1
"BMW 320i"      9735 1
"Datsun 200"    6229 1
"Datsun 210"    4589 1
"Datsun 510"    5079 1
"Datsun 810"    8129 1
"Fiat Strada"   4296 1
end
label values foreign origin
label def origin 0 "Domestic", modify
label def origin 1 "Foreign", modify
[/CODE]  
------------------ copy up to and including the previous line ------------------

Listed 10 out of 74 observations

* Example generated by -dataex-. To install: ssc install dataex
clear
input str18 make int price byte foreign
"Pont. Phoenix" 4424 0
"Pont. Sunbird" 4172 0
"Audi 5000"     9690 1
"Audi Fox"      6295 1
"BMW 320i"      9735 1
"Datsun 200"    6229 1
"Datsun 210"    4589 1
"Datsun 510"    5079 1
"Datsun 810"    8129 1
"Fiat Strada"   4296 1
end
label values foreign origin
label def origin 0 "Domestic", modify
label def origin 1 "Foreign", modify

[Nicolo Framarin]

Hi William Lisowski

This is my postfile code.
I started indicating which variables I need in the new dataset:

* Generating a dataset in which store all relevant statistics
  postfile EU28_stats cnt yrs meany pline k_prisk_bl k_prisk_3b k_pgap_bl k_pgap_3b k_bfaed k_bsaho k_bdihl k_doths k_ben k_tax k_sic k_dec01_ind k_dec02_ind k_dec03_ind ///
     k_dec04_ind k_dec05_ind k_dec06_ind k_dec07_ind k_dec08_ind k_dec09_ind k_dec10_ind k_dec01_ben k_dec02_ben k_dec03_ben k_dec04_ben k_dec05_ben k_dec06_ben ///
     k_dec07_ben k_dec08_ben k_dec09_ben k_dec10_ben k_dec01_tax k_dec02_tax k_dec03_tax k_dec04_tax k_dec05_tax k_dec06_tax k_dec07_tax k_dec08_tax k_dec09_tax ///
     k_dec10_tax k_dec01_net k_dec02_net k_dec03_net k_dec04_net k_dec05_net k_dec06_net k_dec07_net k_dec08_net k_dec09_net k_dec10_net ///
     d_prisk_bl d_prisk_3b d_pgap_bl d_pgap_3b d_bfaed d_bsaho d_bdihl d_doths d_ben d_tax d_sic d_dec01_ind d_dec02_ind d_dec03_ind d_dec04_ind d_dec05_ind d_dec06_ind ///
     d_dec07_ind d_dec08_ind d_dec09_ind d_dec10_ind d_dec01_ben d_dec02_ben d_dec03_ben d_dec04_ben d_dec05_ben d_dec06_ben d_dec07_ben d_dec08_ben d_dec09_ben ///
     d_dec10_ben d_dec01_tax d_dec02_tax d_dec03_tax d_dec04_tax d_dec05_tax d_dec06_tax d_dec07_tax d_dec08_tax d_dec09_tax d_dec10_tax d_dec01_net d_dec02_net ///
     d_dec03_net d_dec04_net d_dec05_net d_dec06_net d_dec07_net d_dec08_net d_dec09_net d_dec10_net ///
     e_prisk_bl e_prisk_3b e_pgap_bl e_pgap_3b e_bfaed e_bsaho e_bdihl e_doths e_ben e_tax e_sic e_dec01_ind e_dec02_ind e_dec03_ind e_dec04_ind e_dec05_ind e_dec06_ind ///
     e_dec07_ind e_dec08_ind e_dec09_ind e_dec10_ind e_dec01_ben e_dec02_ben e_dec03_ben e_dec04_ben e_dec05_ben e_dec06_ben e_dec07_ben e_dec08_ben e_dec09_ben ///
     e_dec10_ben e_dec01_tax e_dec02_tax e_dec03_tax e_dec04_tax e_dec05_tax e_dec06_tax e_dec07_tax e_dec08_tax e_dec09_tax e_dec10_tax e_dec01_net e_dec02_net ///
     e_dec03_net e_dec04_net e_dec05_net e_dec06_net e_dec07_net e_dec08_net e_dec09_net e_dec10_net ///
     using EU28_stats, replace

I don't know if something like what follows could work:

local group = "k d e"
local steps = "bl 3b"
local stat1 = "bfaed bsaho bdihl boths sicer sicct sicee sicse ben tax sic"
local stat2 = "ind ben tax sic"
foreach grp of local group {
   foreach stp of local steps {
      foreach st1 of local stat1 {
         foreach st2 of local stat2 {
            forvalues i=1(1)10 {
               postfile EU28_stats cnt yrs meany pline `grp'_prisk_`stp' `grp'_pgap_`stp' `grp'_`st1' `grp'_dec`i'_`st2' using EU28_stats, replace
            }
         }
      }
   }
}

The code continue as follows:

* Filling the result dataset
  global cnt28 "AT BE BG CY CZ DE DK EE EL ES FI FR HR HU IE IT LT LU LV MT NL PL PT RO SE SI SK UK"
  global years "2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019"
  global group "kids disables elderly"
  global steps "bl 3b"
  foreach cnt of global cnt28 {
     foreach yrs of global years {
        foreach grp of global group {
           foreach stp of global steps {
              foreach st1 of local stat1 {
                 foreach st2 of local stat2 {
                    fovalues i=1(1)10 {
                       post EU28_stats ("`cnt'") (`yrs') (``cnt'_`yrs'_mdy') (``cnt'_`yrs'_pline') (``cnt'_`yrs'_`grp'_prisk_`stp'') (``cnt'_`yrs'_`grp'_pgap_`stp'') ///
                          (``cnt'_`yrs'_`grp'_`st1'') (``cnt'_`yrs'_`grp'_dec`i'_`st2'')
                    }
                 }
              }
           }
        }
     }
  }
  postclose EU28_stats

Now I'm going to run the .do file for just two countries and two years, it could take some minutes...