Exporting tabstat saved results to a new data frame

[Santosh Dash]

Hi,
I am trying to export all information of tabstat results to a new data frame.

Here's the code that worked.

* Example 1

clear
clear frames
sysuse auto
cap frame drop tabstats1
frames create tabstats1 str15(variable) mean sd min max
frames dir
tabstat price, stat(mean sd min max) save
matrix A = r(StatTotal)
frame post tabstats1 ("price") (A[1,1]) (A[2,1]) (A[3,1]) (A[4,1])
frame change tabstats1

In the following example, I want to use the tabstat with by option

* Example 2

clear
clear frames
sysuse auto

cap frame drop tabstats1
frames create tabstats1 str15(variable foreign) mean sd min max
frames dir
tabstat price, stat(mean sd min max) by(foreign) save


frame post tabstats1 ("price") ("domestic") (r(Stat1)[1,1]) (r(Stat1)[2,1]) (r(Stat1)[3,1]) (r(Stat1)[4,1])
frame post tabstats1 ("price") ("foreign") (r(Stat2)[1,1]) (r(Stat2)[2,1]) (r(Stat2)[3,1]) (r(Stat2)[4,1])
*frame post tabstats1 ("price") ("Total") (r(StatTotal)[1,1]) (r(StatTotal)[2,1]) (r(StatTotal)[3,1]) (r(StatTotal)[4,1])
frame change tabstats1

I get the following error message:

invalid syntax
r(198);

Kindly help.

Thank you.

[William Lisowski]

I cannot reproduce your problem using Stata 17.

. clear frames

. sysuse auto
(1978 automobile data)

. 
. cap frame drop tabstats1

. frames create tabstats1 str15(variable foreign) mean sd min max

. frames dir
  default    74 x 12; 1978 automobile data
* tabstats1  0 x 6

Note: Frames marked with * contain unsaved data.

. tabstat price, stat(mean sd min max) by(foreign) save

Summary for variables: price
Group variable: foreign (Car origin)

 foreign |      Mean        SD       Min       Max
---------+----------------------------------------
Domestic |  6072.423  3097.104      3291     15906
 Foreign |  6384.682  2621.915      3748     12990
---------+----------------------------------------
   Total |  6165.257  2949.496      3291     15906
--------------------------------------------------

. 
. 
. frame post tabstats1 ("price") ("domestic") (r(Stat1)[1,1]) (r(Stat1)[2,1]) (r(Stat1)[3,1]) (r
> (Stat1)[4,1])

. frame post tabstats1 ("price") ("foreign") (r(Stat2)[1,1]) (r(Stat2)[2,1]) (r(Stat2)[3,1]) (r(
> Stat2)[4,1])

. frame post tabstats1 ("price") ("Total") (r(StatTotal)[1,1]) (r(StatTotal)[2,1]) (r(StatTotal)
> [3,1]) (r(StatTotal)[4,1])

. frame change tabstats1

. list

     +----------------------------------------------------------+
     | variable    foreign       mean         sd    min     max |
     |----------------------------------------------------------|
  1. |    price   domestic   6072.423   3097.104   3291   15906 |
  2. |    price    foreign   6384.682   2621.915   3748   12990 |
  3. |    price      Total   6165.257   2949.496   3291   15906 |
     +----------------------------------------------------------+

.

[Santosh Dash]

William Lisowski, Thank you for your reply. I am using Version 16. Not sure why it did not work in Version 16.

My question is if the code written above is correct, do I have to manually type all the codes for Stata to export it to a new frame? Suppose, I am doing tabstat of income for all the US states. So, I have to write 51 lines of "frames post" so that Stata can save results in a new frame. If yes, it is actually more time-consuming than simply copy-pasting the tabstat results from the Stata editor. Is there a better way to handle this?

Would appreciate it if someone could suggest how to go about this problem.

[William Lisowski]

You show us an error message and you do not show us which command generated the error message.

Perhaps you are running version 15 rather than version 16, because from help whatsnew15to16 we read

8. Matrix subscripting and extraction are now allowed with r() and e() matrices, including
e(b), e(V), and e(Cns).

so in earlier versions

... r(Stat1)[1,1] ...

would have been a syntax error.

Here is how I would modify your code to handle an arbitrary number of values of the by variable.

clear
clear frames
sysuse auto

cap frame drop tabstats1
frames create tabstats1 str15(variable foreign) mean sd min max
frames dir
tabstat price, stat(mean sd min max) by(foreign) save

local i 0
while "`r(name`++i')'"!="" {
    matrix s = r(Stat`i')
    frame post tabstats1 ("price") ("`r(name`i')'") (s[1,1]) (s[2,1]) (s[3,1]) (s[4,1])
}
matrix s = r(StatTotal)
frame post tabstats1 ("price") ("Total") (s[1,1]) (s[2,1]) (s[3,1]) (s[4,1])
frame change tabstats1

list

. list

     +----------------------------------------------------------+
     | variable    foreign       mean         sd    min     max |
     |----------------------------------------------------------|
  1. |    price   Domestic   6072.423   3097.104   3291   15906 |
  2. |    price    Foreign   6384.682   2621.915   3748   12990 |
  3. |    price      Total   6165.257   2949.496   3291   15906 |
     +----------------------------------------------------------+

[Santosh Dash]

William Lisowski, Thank you for kindly providing the solution. My Stata version is 16 as the following code shows. However, when I ran the same code, I could not reproduce your tables. I have posted the codes and results. Not sure what is going on.

. version
version 16.0

sysuse auto
(1978 Automobile Data)

cap frame drop tabstats1
frames create tabstats1 str15(variable foreign) mean sd min max
frames dir

  default    74 x 12; 1978 Automobile Data
* tabstats1  0 x 6

Note: frames marked with * contain unsaved data

tabstat price, stat(mean sd min max) by(foreign) save

local i 0
while "`r(name`++i')'"!="" {
    matrix s = r(Stat`i')
    frame post tabstats1 ("price") ("`r(name`i')'") (s[1,1]) (s[2,1]) (s[3,1]) (s[4,1])
 }

matrix s = r(StatTotal)
frame post tabstats1 ("price") ("Total") (s[1,1]) (s[2,1]) (s[3,1]) (s[4,1])
frame change tabstats1

. list

     +----------------------------------------------------------+
     | variable    foreign       mean         sd    min     max |
     |----------------------------------------------------------|
  1. |    price   Domestic   6072.423   3097.104   3291   15906 |
  2. |    price      Total          .          .      .       . |
     +----------------------------------------------------------+

[William Lisowski]

Please follow the step-by-step instructions below to prvide useful information for debugging your problem, especially since you are running Stata 16 and I do not have access to Stata 16.

1) Copy this block of code.

clear
clear frames
sysuse auto

cap frame drop tabstats1
frames create tabstats1 str15(variable foreign) mean sd min max
frames dir
tabstat price, stat(mean sd min max) by(foreign) save
return list

local i 0
while "`r(name`++i')'"!="" {
    matrix s = r(Stat`i')
    frame post tabstats1 ("price") ("`r(name`i')'") (s[1,1]) (s[2,1]) (s[3,1]) (s[4,1])
}
matrix s = r(StatTotal)
frame post tabstats1 ("price") ("Total") (s[1,1]) (s[2,1]) (s[3,1]) (s[4,1])
frame change tabstats1

list

2) In Stata open an empty Do-file Editor window and paste the copied code into the window.

3) In the Do-file Editor window, paste the "Do" button at the top, or from Stata's View menu, choose Do-file Editor > Execute (do) from the top.

If that works, you can stop, the problem in post #5 lay in how you ran the code suggested in post #4, which is why these instructions are so explicit.

If it did not work, continue with the next two steps so that there will be sufficient information presented to attempt to solve the problem.

4) In Stata's Results window, select and copy everything from the "clear" command through the "end of do-file" message.

5) On Statalist, in your next reply on this topic, paste what you have copied, as I have below, using code delimiters [CODE] and [/CODE].

. clear

. clear frames

. sysuse auto
(1978 automobile data)

.
. cap frame drop tabstats1

. frames create tabstats1 str15(variable foreign) mean sd min max

. frames dir
  default    74 x 12; 1978 automobile data
* tabstats1  0 x 6

Note: Frames marked with * contain unsaved data.

. tabstat price, stat(mean sd min max) by(foreign) save

Summary for variables: price
Group variable: foreign (Car origin)

 foreign |      Mean        SD       Min       Max
---------+----------------------------------------
Domestic |  6072.423  3097.104      3291     15906
 Foreign |  6384.682  2621.915      3748     12990
---------+----------------------------------------
   Total |  6165.257  2949.496      3291     15906
--------------------------------------------------

. return list

macros:
              r(name2) : "Foreign"
              r(name1) : "Domestic"

matrices:
              r(Stat2) :  4 x 1
              r(Stat1) :  4 x 1
          r(StatTotal) :  4 x 1

.
. local i 0

. while "`r(name`++i')'"!="" {
  2.     matrix s = r(Stat`i')
  3.     frame post tabstats1 ("price") ("`r(name`i')'") (s[1,1]) (s[2,1]) (s[3,1]) (s[4,1])
  4. }

. matrix s = r(StatTotal)

. frame post tabstats1 ("price") ("Total") (s[1,1]) (s[2,1]) (s[3,1]) (s[4,1])

. frame change tabstats1

.
. list

     +----------------------------------------------------------+
     | variable    foreign       mean         sd    min     max |
     |----------------------------------------------------------|
  1. |    price   Domestic   6072.423   3097.104   3291   15906 |
  2. |    price    Foreign   6384.682   2621.915   3748   12990 |
  3. |    price      Total   6165.257   2949.496   3291   15906 |
     +----------------------------------------------------------+

.
end of do-file

[Santosh Dash]

Dear Prof. Lisowski,

My apologies for not articulating my problem properly. I exactly did (Steps 1 to 3 of your reply in #6) when I replied in #5. But I did not post the code like the way you have suggested in #6. Following your suggestion, I again ran the code completely and find the problem to persist. I have posted here the code I ran for your kind perusal. Thank you once again, William Lisowski .

. clear

. clear frames

. sysuse auto
(1978 Automobile Data)

. 
. cap frame drop tabstats1

. frames create tabstats1 str15(variable foreign) mean sd min max

. frames dir
  default    74 x 12; 1978 Automobile Data
* tabstats1  0 x 6

Note: frames marked with * contain unsaved data

. tabstat price, stat(mean sd min max) by(foreign) save

Summary for variables: price
     by categories of: foreign (Car type)

 foreign |      mean        sd       min       max
---------+----------------------------------------
Domestic |  6072.423  3097.104      3291     15906
 Foreign |  6384.682  2621.915      3748     12990
---------+----------------------------------------
   Total |  6165.257  2949.496      3291     15906
--------------------------------------------------

. return list

macros:
              r(name2) : "Foreign"
              r(name1) : "Domestic"

matrices:
              r(Stat2) :  4 x 1
              r(Stat1) :  4 x 1
          r(StatTotal) :  4 x 1

. 
. local i 0

. while "`r(name`++i')'"!="" {
  2.     matrix s = r(Stat`i')
  3.     frame post tabstats1 ("price") ("`r(name`i')'") (s[1,1]) (s[2,1]) (s[3,1]) (s[4,1])
  4. }

. matrix s = r(StatTotal)

. frame post tabstats1 ("price") ("Total") (s[1,1]) (s[2,1]) (s[3,1]) (s[4,1])

. frame change tabstats1

. 
. list

     +----------------------------------------------------------+
     | variable    foreign       mean         sd    min     max |
     |----------------------------------------------------------|
  1. |    price   Domestic   6072.423   3097.104   3291   15906 |
  2. |    price      Total          .          .      .       . |
     +----------------------------------------------------------+

. 
end of do-file

[William Lisowski]

I suspect your copy of Stata 16 is not fully updated. There was a problem in frame post that caused it to delete the returned results, and it was fully corrected in the 16oct2019 update. This problem would explain your results in post #2 - the second frame post would produce a syntax error because r(Stat2) would no longer exist - and in post #7 - after the first frame post both r(Stat2) and r(StatTotal) would no longer exist, so the matrix s would be empty.

You should run the about command, which will produce output like the following.

. about

Stata/SE 17.0 for Mac (Intel 64-bit)
Revision 16 Dec 2021

followed by other stuff that's not important here. Your output should show 16.1 where mine shows 17.0 and should show 7 Dec 2021 where mine shows 16 Dec 2021. (Yes, Stata continues to release updates to older versions even after a newer version come out.)

If you do not have the latest version of Stata 16.1 then you should update your installation of Stata using the update command: see the output of help update for details. This needs to be done from an account with administrator privileges on your computer.

[Santosh Dash]

Thank you a lot, Prof. Lisowski for kindly debugging the problem. I found out that my Stata version is 16.0. That is why I am not getting the desired results. Thank you once again.