Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Looping, putexcel with a range of variables and variation on the by

    Hello helpful Stata users

    I require (possibly nested?) loop helpt. I have already seen the thread, blog and entries on this, but I need help if possible!

    I am required to produce rows of summary statistics for a range of continuous variables by a range of categorical variables within sets of dichotomous variables. I do not need the totals.

    e.g. (from Stata dataset auto)

    r(N) r(min) r(max) r(sd) r(p50) r(p10) r(p75) of price by make
    r(N) r(min) r(max) r(sd) r(p50) r(p10) r(p75) of weight by make
    r(N) r(min) r(max) r(sd) r(p50) r(p10) r(p75) of price by make, for each value of rep78
    r(N) r(min) r(max) r(sd) r(p50) r(p10) r(p75) of weight by make, for each value of rep78
    etc.

    I have written the following

    cd "C:"
    Sysuse foreign

    putexcel set myresult
    putexcel set myresult, replace sheet("Autot1")
    local row=2
    foreach x of varlist weight length {
    tabstat `x', by(foreign) statistics (n min max mean sd med p25 p75) save
    matrix Stat2 = r(Stat2)'
    matrix Stat1 = r(Stat1)'
    putexcel A`row'=("`x'")
    putexcel C`row'= matrix(Stat2)
    putexcel D`row'= matrix(Stat1)
    local ++row
    }

    The data are displaying correctly in that the summary statistics are in the same row as the name of the variable and each variable is under the next one.

    However, the matrices are writing over each other, so only the final row for each variable is displayed in the excel sheet

    Last, I do not understand how to set the variables to appear in the by() command. I think I need a nested loop, but I cannot work it out

    Many thanks









  • #2
    Something like?

    Code:
    sysuse auto, clear
    putexcel set myresult, replace
    putexcel set myresult, replace sheet("Autot1")
    local row=2
    foreach x of varlist weight length {
    tabstat `x', by(foreign) statistics (n min max mean sd med p25 p75) save
    mat Stat=  (r(Stat2),r(Stat1))'
    putexcel A`row'=("`x'")
    putexcel C`row'= matrix(Stat)
    putexcel B`row'= "`:lab (foreign) 1'"
    local ++row
    putexcel B`row'="`:lab (foreign) 0'"
    local ++row
    }
    Res.:

    Click image for larger version

Name:	Capture.PNG
Views:	1
Size:	29.9 KB
ID:	1710976

    Comment


    • #3
      Andrew Musau - that has worked brilliantly, thank you so much. I will just write a series of loops for the different by variables

      Comment


      • #4
        Dear Andrew Musau

        I was hoping you might be so kind as to help me answer the last part of my query? I am required to add another level to the loop, so for each category of x, I have excel output of summary statistics of the continuous variable y by another variable z

        for group x1 sum y by(z)
        for group x2 sum y by(z)

        where x is a categorical variable with values 1,2

        I have written below, but it is not running.

        Code:
        sysuse auto, clear
        putexcel set myresult, replace
        putexcel set myresult, replace sheet("Auto2")
        local row=2 
        su foreign, meanonly
        forval i=1/`r(max)' { 
            foreach x of varlist weight length { 
                tabstat `x' if foreign==`i' , by(rep78) statistics (n min max mean sd med p25 p75) save 
                mat Stat= (r(Stat2),r(Stat1))' 
                putexcel A `row'="`:lab (foreign ) 0'" 
                putexcel A `row'="`:lab (foreign ) 1'" 
                putexcel B`row'=("`x'") 
                putexcel D`row'= matrix(Stat) 
                putexcel C`row'= "`:lab (rep78) 1'" 
                local ++row 
                putexcel C`row'="`:lab (rep78) 2'" 
                local ++row 
                putexcel C`row'="`:lab (rep78) 3'" 
                local ++row 
                putexcel C`row'="`:lab (rep78) 4'" 
                local ++row 
                putexcel C`row'="`:lab (rep78) 5'" 
             } 
        }
        Last edited by Amy Dillon; 24 Apr 2023, 05:17. Reason: edit of code presentation

        Comment


        • #5
          As you do not know what values the levels of the categorical variable take, use -levelsof- and -foreach- in place of -forval-. The same applies to the last set of commands - e.g., not all levels of rep78 are observed for foreign cars in the auto dataset, so the summaries will only relate to the observed levels. You can include multiple by variables in a nested loop. The results below appear duplicated as my second by var rep78_2 is a clone of rep78.

          Code:
          sysuse auto, clear
          gen rep78_2=rep78
          lab define rep78 1 "Poor" 2 "Fair" 3 "Average" 4 "Good" 5 "Excellent"
          lab values rep78 rep78
          lab values rep78_2 rep78
          putexcel set myresult, replace
          putexcel set myresult, replace sheet("Auto2")
          local row 2
          qui levelsof foreign, local(flevels)
          foreach l of local flevels{
              foreach x of varlist weight length{
                  foreach byvar of varlist rep78 rep78_2 { 
                      qui levelsof `byvar' if foreign==`l', local(count)
                      tabstat `x' if foreign==`l' , by(`byvar') statistics (n min max mean sd med p25 p75) save
                      local Stat r(Stat1)
                      forval c= 2/`=wordcount("`count'")'{
                          local Stat "`Stat', r(Stat`c')"
                      }
                      mat Stat= (`Stat')'
                      putexcel A`row'="`:lab (foreign) `l''" 
                      putexcel B`row'=("`x'")
                      putexcel D`row'= matrix(Stat)
                      putexcel C`row'= "`:lab (`byvar') `=word("`count'", 1)''"
                      local ++row
                      forval i=2/`=wordcount("`count'")'{
                          putexcel C`row'="`:lab (`byvar')  `=word("`count'", `i')''"
                          local ++row
                      }
                  }
              }
          }
          Res.:
          Click image for larger version

Name:	Capture.PNG
Views:	1
Size:	88.7 KB
ID:	1711021

          Comment

          Working...
          X