Announcement

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

  • puexcel with command tabulate

    Hello everyone,

    I am currently experimenting with "putexcel" on STATA16.
    I have no problem reporting the results of a simple tabulation but when I want to add the percentages in columns it gets complicated.

    I would like to have my Excel file in this form:
    No additionnal time Additionnal time p-value
    N % N %
    Sexe 0,687853
    Male 201 58.60 459 57.74
    Female 142 41.40 336 42.26
    With the following code I can only display the number of employees:

    putexcel A7="Sexe", bold
    tab I_SEXE class_add_time0, missing col chi2 matcell(freq)
    putexcel B8=matrix(freq)
    putexcel F7=matrix(r(p))
    putexcel A8="Male" A9="Female"
    No additionnal time Additionnal time p-value
    N % N %
    Sexe 0,786233
    Male 201 459
    Female 142 336
    Thank you in advance for your help.

    Yours sincerely
    Joséphine

  • #2
    Consider estout(SSC) for this. Some code examples are available at http://repec.org/bocode/e/estout/estpost.html

    Comment


    • #3
      You can also try asdoc. Here is an example:

      Code:
      ssc install asdoc, replace
      sysuse nlsw88
      asdoc tab industry union, replace
      Click image for larger version

Name:	Capture.JPG
Views:	1
Size:	92.3 KB
ID:	1593946


      asdocx can export to LaTeX or Excel.
      Regards
      --------------------------------------------------
      Attaullah Shah, PhD.
      Professor of Finance, Institute of Management Sciences Peshawar, Pakistan
      FinTechProfessor.com
      https://asdocx.com
      Check out my asdoc program, which sends outputs to MS Word.
      For more flexibility, consider using asdocx which can send Stata outputs to MS Word, Excel, LaTeX, or HTML.

      Comment


      • #4
        Thank you for your answers.
        I tried "estpost" for a two-way frequency table (tabulate) but I can't manage to recover only a part of the percentages to introduce them where I want in the Excel file.

        Comment


        • #5
          Here is an example.

          Code:
          sysuse auto
          tab foreign rep78 if rep78>3, missing col chi2 matcell(freq)
          Code:
          . tab foreign rep78 if rep78>3, missing col chi2 matcell(freq)
          
          +-------------------+
          | Key               |
          |-------------------|
          |     frequency     |
          | column percentage |
          +-------------------+
          
                     |        Repair Record 1978
            Car type |         4          5          . |     Total
          -----------+---------------------------------+----------
            Domestic |         9          2          4 |        15
                     |     50.00      18.18      80.00 |     44.12
          -----------+---------------------------------+----------
             Foreign |         9          9          1 |        19
                     |     50.00      81.82      20.00 |     55.88
          -----------+---------------------------------+----------
               Total |        18         11          5 |        34
                     |    100.00     100.00     100.00 |    100.00
          
                    Pearson chi2(2) =   5.8651   Pr = 0.053
          What do you need in your table?

          Comment


          • #6
            Thank you for your response.

            I would like to enter the sums but also the percentages in Excel.
            In this example I would like to have the figures in this order:
            N % N % N %
            Domestic 9 50.00 2 18.18 4 80.00
            Foreign 9 50.00 9 81.82 1 20.00
            Thank you in advance

            Comment


            • #7
              Code:
              sysuse auto, clear
              eststo clear
              estpost tab foreign rep78 if rep78>3, missing chi2
              qui esttab, cell(colpct(fmt(2))) unstack drop(Total: Total) noobs
              mat w= r(coefs)
              qui esttab, cell(b) unstack drop(Total: Total) noobs
              mat w= r(coefs), w
              esttab mat(w) using myfile.csv,  unstack collabels("N" "%" "N" "%" "N" "%") ///
              eqlabels("Category 1" "Category 2" "Missing") nomtitle compress replace
              Res.:

              Code:
              . esttab mat(w),  unstack collabels("N" "%" "N" "%" "N" "%") ///
              > eqlabels("Category 1" "Category 2" "Missing") nomtitle compress replace
              
              ----------------------------------------------------------------------
                         Categor~1           Categor~2             Missing          
                                 N         %         N         %         N         %
              ----------------------------------------------------------------------
              Domestic           9        50         2  18.18182         4        80
              Foreign            9        50         9  81.81818         1        20
              ----------------------------------------------------------------------
              The code above exports the table as a csv file which you can open in Excel. But, you can also use putexcel if you want. Just extract the matrix from esttab.

              Code:
              sysuse auto, clear
              eststo clear
              estpost tab foreign rep78 if rep78>3, missing chi2
              qui esttab, cell(colpct(fmt(2))) unstack drop(Total: Total) noobs
              mat w= r(coefs)
              qui esttab, cell(b) unstack drop(Total: Total) noobs
              mat w= r(coefs), w
              esttab mat(w),  unstack collabels("N" "%" "N" "%" "N" "%") ///
              eqlabels("Category 1" "Category 2" "Missing") nomtitle compress replace
              mat w= (r(coefs))'
              mat l w
              Res.:

              Code:
              . mat w= (r(coefs))'
              
              . mat l w
              
              w[2,6]
                                      4:          4:          5:          5:  _missing_:  _missing_:
                               Domestic     Foreign    Domestic     Foreign    Domestic     Foreign
                   active:b           9           9           2           9           4           1
              active:colpct          50          50   18.181818   81.818182          80          20

              Comment


              • #8
                Thank you for your response.
                I'm almost there... in the csv my numbers are in the right direction (like your example, category 1 on the left and category 2 on the right).
                But here I still have my 2 lines underneath (in red) and not on the right.
                No additionnal time Additionnal time p-value
                N % N %
                Sexe 0,786233
                Male 201 58,60058
                Female 142 41,39942
                459 57,73585
                336 42,26415
                My code :

                estpost tab I_SEXE class_add_time0, missing chi2
                qui esttab, cell(colpct(fmt(2))) unstack drop(Total: Total) noobs
                mat w= r(coefs)
                qui esttab, cell(b) unstack drop(Total: Total) noobs
                mat w= r(coefs), w
                esttab mat(w) using myfile.csv, unstack collabels("N" "%" "N" "%") ///
                eqlabels("No additionnal time" "Additionnal time") nomtitle compress replace

                putexcel B8=mat(w)

                Thanks

                Comment


                • #9
                  You need to write a program to unstack the matrix equations. This will do it.

                  Code:
                  sysuse auto, clear
                  eststo clear
                  estpost tab foreign rep78 if rep78>3, missing chi2
                  qui esttab, cell(colpct(fmt(2))) unstack drop(Total: Total) noobs
                  mat w= r(coefs)
                  qui esttab, cell(b) unstack drop(Total: Total) noobs
                  mat w= r(coefs), w
                  local cols: roweq(r(coefs))
                  local cols: list uniq cols
                  local i 1
                  foreach colname of local cols{
                      mat w`i'= w["`colname':", 1..2]
                      local ++i
                  }
                  local w
                  forval j=1/`=`i'-2'{
                      local w "`w' w`j',"
                  }
                  local w = "`w' w`=`i'-1'"
                  mat w= `w'
                  Res.:


                  Code:
                  . mat l w
                  
                  w[2,6]
                                 active:    active:    active:    active:    active:    active:
                                      b     colpct          b     colpct          b     colpct
                  4:Domestic          9         50          2  18.181818          4         80
                   4:Foreign          9         50          9  81.818182          1         20
                  I leave you to change the matrix row and column names + row and column equation names before using putexcel to export to Excel.
                  Last edited by Andrew Musau; 12 Feb 2021, 17:25.

                  Comment

                  Working...
                  X