Help with Exporting Data to Separate Excel Tabs Based on Property IDs

[Dylan Kosloskus]

Hello all,

I am working on exporting dataset subsets to separate Excel tabs, each named after property IDs from a real estate dataset. I've used a loop with levelsof to dynamically create sheet names based on property values and then export each property's data to its corresponding tab. However, I'm encountering an error that says "p invalid name r(198)" when executing the loop. Here's the relevant part of my code:

levelsof property, local(properties)

// Looping through properties & exporting to tabs
foreach p of local properties {
    local sheet_name = string(`p', "%12.0g")  // Converting num to str
    display "Exporting property: `sheet_name'"  // debug check
    preserve
        keep if property == `p`
        export excel using "O:\monthly financial review\Submetering Data by Property and Month.xlsx", sheet("`sheet_name'") firstrow(variables) replace
    restore
}

The error occurs during the loop, specifically when trying to use p as a part of the export excel command. Here is the error:


7. restore
8. }
Exporting property: 101
`p` invalid name
r(198);


Could anyone provide any insight on why this error is occurring and how to resolve it?

Any advice or alternative approaches would be greatly appreciated! Thank you so much!

[Clyde Schechter]

When you write

keep if property == `p`

you have mangled the closing quote of the local macro. It should be

keep if property == `p'

As an aside, this code is inefficient, and if you have a data set with a large number of properties, it will run for a very long time. Each time you -preserve- and -restore- you are thrashing the disk, and I/O is the slowest thing you can do in computing. You should keep I/O operations in your code to the minimum necessary to get the job done. Instead of what you have, get rid of the -preserve- and -restore- commands and just do this:

display "Exporting property: `sheet_name'"  // debug check
export excel if property == `p' using "O:\monthly financial review\Submetering Data by Property and Month.xlsx", sheet("`sheet_name'") firstrow(variables) replace

Yes, those -if property == `p'- operations are also expensive, but not nearly as much as reading and writing the entire data set to disk. (And if it's still really slow with -if property == `p'-, check out the -runby- command (by Robert Picard and me, available from SSC) for an even faster way.