Stata creates an Excel spreadsheet with the putexcel command
This article demonstrates how to create a custom report for any variable. Create a table that combines the cell count with the row percentage and the standard deviation method. But you can modify the following examples to include the percentage of the column, percentage, standard error, confidence interval, or any statistics. Use a local macro to pass the variable name to my program. You can create the same report for any variable by assigning a new variable name to the macro. You can extend this idea by creating a do-file for each report and passing the variable name to the do-file. This is another important step in our automatic creation of reports in Excel.
This article is very long and contains several large code groups. This is because each example contains the code from the previous example and the new line of code. While allowing new code to be seen throughout the context of the program, it also takes longer to appear with new code. Most of the code in the code group is the same as the example.
Case 1: Write the returned results to Excel
Start using tabulate to create a cell count matrix for sex and race. Type
But I want the ability of any two categorical variables. So store the sex in the local macro RowVar and store the race in the local macro ColVar. You can now list sex and race as tables using the appropriate local macros.
The cell count is stored in the matrix cellcounts.
Tabulate to enter the return list to see the scalar return list. The total number of observations is stored in the scalar R(n), the number of rows is stored in R(r), and the number of columns is stored in R(c).
In order to use these scalars later, you can store them in a local macro.
Case 2: Loop rows and columns
I can use the count of stored rows and columns to loop through each cell of the matrix cellcounts.
Looping each cell in the matrix allows us to format the numbers in each cell and/or the numbers used to calculate another quantity, such as a percentage.
Case 3: The function of char()
The rows and columns of the matrix are all indexed by numbers. The rows of the Excel table are indexed by numbers, and the columns are indexed by letters. Use the char() function in Excel to convert the column numbers of a matrix into column letters. The argument to the char() function is an ASCII code and a function that returns the corresponding ASCII code. For example, char(65) returns the letter "A" char (66) returns the letter "B" and so on.
You can use the function char(64 + `col') in a loop to convert the column numbers in the matrix to the column letters of an Excel table. Line 4 in Code 1 below stores the cell name in the local macro Cell. You can use the fourth line "`row'" instead of string(`row'). But I always need the function of string(), so I still use it for consistency. Line 5 uses putexcel to write the value of Cell corresponding to the cell in Excel.
Case 4: Writing cell counts to Excel
Code Group 2 demonstrates how to write cell counts in a matrix to an Excel table. The fourth row cell count of code group 2 is stored from the matrix cellcounts to the local macro CellContents, the fifth row of the Excel table stores the target cell to the local macro Cell, and the sixth row uses the putexcel to write the CellContents in the Excel table. In the Cell.
Case 5: Write the line percentage to Excel
I want to write the line percentage to the Excel table instead of the cell count. I need two cell counts and row totals to calculate the line percentage. The tabulate command in the third line below code group 3 stores the row totals in the vector rowtotals. Line 11 stores the cell count in the local macro cellcount. The 12 lines calculate and format the cell percentage and store it in the local macro cellpercent. Line 13 adds the "%" symbol to cellpercent and stores the generated string into the local macro CellContents.
Case 6: Write cell counts and row percentages to Excel
Write cell counts and row percentages to each cell. This can be done by modifying line 13 of code group 3 above. The thirteenth line of code group 4 below stores both cellcount and cellpercent in the local macro CellContents.
Case 7: Adding a row label to an Excel table
Next add a row label to the Excel table. You can type "female" and "male" in the Excel table, but I want to be able to change the line variable at the top of the program and automatically mark the line. Value labels such as "0 = female" and "1 = male" are defined using label define and the label is attached to the label values ​​variable. If you enter describe sex, you can see that the attached value tag sex is named SexLabel.
The definition of SexLabel can be seen by entering the label list SexLabel.
Variable names can be used to access all information about variable value tags. The name of the current row variable, sex is stored in the local macro RowVar. You can use the following macro list function to store the value tag RowVar into the local macro RowValueLabel.
You can use levelsof to store RowVar's numeric categories into the local macro RowLevels.
You can refer to each of the numeric categories in RowLevels to use the words() function. For example, the first "word" in the local macro RowLevels is "0". The "word" can be stored in the local macro RowValueLabelNum by using the macro list function below.
The tag associated with "0" can be stored in the local macro RowLabel by using the macro list function below.
Use the same method to store the second type of sex.
The following code group 5 contains these commands for extracting the value tag of RowVar and using putexcel to write the tag to the Excel table.
First move the cells of the Excel table down one row and one column to the right. This will make room for the row and column labels added later. Add 1 to the char() and string() functions on lines 16 and 23 below to change the table down and to the right.
The value tag of row 8 RowVar is stored in the local macro RowValueLabel. Line 9 RowVar number types are stored in the local macro RowLevels.
Lines 14-17 extract the labels associated with each number category and write them to an Excel spreadsheet.
Case 8: Adding row totals to an Excel table
Add the row totals to the Excel table below. Store the row totals in the previous matrix rowtotals so that the percentage of the rows can be calculated. So you need to write the value of the matrix to the Excel table.
Line 3 of code group 6 below stores the row totals into the matrix rowtotals. Lines 9-12 extract the total number of each row in rowtotals and write it to the Excel table. Note that by adding 2 to 20 lines of the char() function, write the total of one column to the right of the Excel table.
Case 9: Adding column labels and totals to an Excel table
Use the same method to increase column labels and totals. In line 4 of code group 7 below, the column is totaled into the matrix coltotals. Line 12 stores the value tag ColVar into the local macro ColLevels, and line 13 stores the numeric category of ColVar into the local macro ColLevels.
Lines 34-43 write the value tags and column totals into an Excel form. You only need to write the information to Excel once. When the condition satisfies if `row'==1, I can specify to run only those lines.
Case 10: Forming an Excel Table
Now that I have all the numbers and labels in the Excel table, I want to add some lines to make it easier to read.
In lines 49-50 of code group 8, the total count entered is in the lower right corner of the table. Lines 52-53 mark the total bars and 55-56 mark the rows.
I want to add a row to a table by specifying a range of cells. Can do more simple than 58-73 lines but the code will be very
Hard to read. Lines 58-61 store the cells defining the four corners of the table to the local macro UpperLeft, UpperRight,
BottomLeft, and BottomRight. Lines 63-73 use these 4 cells to define the cell area using the Add Line to the EXCEL form.
Case 11: Creating an Excel Table for Any Variable
At this point you may wonder if you need to take the time to use the strategy to write the form to Excel. You can take a moment to write this program to manually create an Excel table. But I want to create this table and need to use it multiple times in the future, this strategy will save a lot of time. For example, rows and columns in a table can be toggled by simply switching the variable assignments in rows 1 and 2 of code group 9 below.
Case 12: Creating a table for continuous variables at the categorical variable level
Case 1-11 demonstrates how to create a table for two categorical variables. A similar method is used to create a summary statistics table for continuous variables with super-level categorical variables.
Start by creating a column header for the categorical variable race. The code in code group 10 below looks like the code used in case 1-11. The only unfamiliar code appears on line 6, storing the variable tag in the local macro ColVarLabel. The variable tag is then written to the merged cell above the 25 and 26 row column labels.
Case 12 (continued):
Next, you can add a row that contains the average and standard deviation of each level of the race. In the second line of code group 11 below, store age to the local macro ContVar.
The condition for calculating the mean and standard deviation on line 12 is if `ColVar'==`ColLevel' is true. You may want to use the condition if `ColVar'==`col' but it is best to avoid it. The level of categorical variables is often an integer from the beginning of the numbering order (such as "1, 2, 3.."). But this is not always true. For example, the indicator variable, like gender, is numbered starting from 0. Sex has 2 categories, so if we use the condition if sex==1, we will see the male result but will not see the result of if sex==2. Even the condition if sex==0 will not be considered.
The 21 lines of typography return the average r(mean), which is displayed to a decimal point location and stored in the local macro RowMean. The standard deviation r(sd) returned by the 22-line layout is displayed to a decimal point location and stored in the local macro RowSD. Line 15 combines RowMean and RowSD to store the results in the local macro CellContents. The 20-25 behavior column repeats these calculations in total, and lines 26-27 write the variable name age to the Excel table.
Conclusion
I hope this article will inspire you rather than scare you. Creating automatic reports for any variable is not a quick and easy task.
But manually generating these forms can be time consuming and frustrating. If you've ever spent time composing a form for a manuscript, one reviewer thinks that your modified analysis has changed all the forms to some extent, and you should understand what I'm talking about. Or run weekly or monthly reports of regularly updated data. In the long run, writing programs to automate Excel tables can save more time in the future. Errors that may occur when manually creating a table will be eliminated.
For more information and tutorials on Stata software, visit the China Science Software Network for more information.
Hanging Hooks,Hanging Hook,Wall Hooks For Hanging,Decorative Hanging Hooks
Jiangmen Jianghai Jianshang Houseware Co.,LTD. , https://www.jm-jianshang.com