Using putexcel to write variable values for records in a data set

[James Swartz]

I think this is a rather simple problem but the solution is escaping me. I have a data set of some number of records and variables. The data have to be written into an Excel file with a very specific format and a specific variable order. I need to massage the raw data some and then I want to write the data to a preformatted Excel file that has the variable names in row 1.

Ideally, the foreach loop would execute the putexcel statements, move to the next record in the data set, and write the values from that record into the next line in the Excel file. But what happens with the code below is that the values from the very first record in the Stata data set get written to however many rows are specified in the loop. In this case, there were 4 records in the test data set I am working with. The data were written to Excel in the correct locations but the data from record 1 was repeated across all 4 rows.The loop does not advance across the records in the data set.

How do I tell Stata to write a row of data, proceed to the next record in the data file, and write the date from that record into the next row of the Excel file, repeating this until the end of the Stata data set has been reached and there is a separate row in the Excel file for each record in the data set?

I know I probably could figure out a way using order and export to make it work but I would prefer to manage it this way if possible.

Thanks for any help.

James

putexcel set "HAS NEXA GPRA $year - Quarter $quarter.xlsx", modify sh("CSATGPRAClientOutcomeMeasuresCS")

forvalues i = 2/$rec_total {
    quietly: putexcel A`i' = has_client_id
    quietly: putexcel B`i' = grant_type
    quietly: putexcel C`i' = 0
    quietly: putexcel D`i' = contract_id
    quietly: putexcel E`i' = interview_type
    quietly: putexcel F`i' = interview_type
    quietly: putexcel G`i' = (baseline_interview_date)
}

[Leonardo Guizzetti]

This is exactly what -export excel- is for. It will be faster too since you won’t have to repeatedly open and save the excel file.

[Leonardo Guizzetti]

...
I know I probably could figure out a way using order and export to make it work but I would prefer to manage it this way if possible.
...

There's no need to rearrange variable order. -export excel- (and many other Stata commands) rarely care about the variable order submitted in the variable list matching the order of variables in the dataset.

To expand a bit on my suggestion to avoid -putexcel- for this task:
1) It's inefficient in general to explicitly loop over observations one-by-one, in Stata. With -putexcel set- specifically used how you have done, it's extra inefficient because every -putexcel- statement will cause Stata to open, modify and then save the Excel file. This can be modified o open the file once in memory and one save only after all changes are made, but it's still not helpful.
2) -putexcel- was not not intended to be used to export datasets, possibly because existing tools do this well enough already. Nevertheless, you will generally be exporting unlabeled and unformatted data values, which is often not desirable.

Here's an example describing what I suggest in #2.

sysuse auto
export excel foreign price make using "myexcel.xlsx" in 3/10 , sheet("Data", modify) cell(A4)

Stata has exported the data with data labels and formatting present, using an arbitrary subset of the dataset. You can explore the help for additional options, such as how to include varaible names or labels as a header.

[James Swartz]

Thanks Leonardi,

I will give that a try. I also learned - with some embarrassment - that I never needed to repeat putexcel for each cell or piece of information I wanted to write out. Your comment about constantly opening and closing the Excel file keyed that insight. I thought the way it worked was that you open the Excel file with 'putexcel set' and then the file remained open to execute multiple putexcel commands that wrote data to the open file until you close the file later. I think I have several files with hundreds of lines of code where I used putexcel and wrote to different cells in the Excel file. Putexcel works in that context because I am writing out results sets instead of the variable data in the file. The good news is that I can rewrite all that unnecessary and inefficient code. The bad news is I have to go in and modify hundreds of lines of code!

I will give export excel a shot and see if I can get it to work correctly.

Thanks again for taking the time to respond.