Reproducing lognormal Stata graphs in Excel

[Megan Denver]

Hi everyone,

I’m trying to replicate lognormal hazard graphs that Stata 12.1 produces in Excel so I can better understand and describe how the covariates affect the shape of the curve. Most of the literature in my field focuses on changes in level, rather than shape. In Stata, I stset the data using a time variable in days, and my failure variable is a one-time occurrence (first rearrest). Here’s an example of what I ran in Stata to look at males in my sample, focusing in on the first month:

streg if male==1 & timedays<30, distribution(lognormal)
stcurve, haz

[See Stata image attached]
Lognormal images.docx

(If it’s helpful, there are approximately 1,000 males in this sample who failed in the timeframe above.) Then, to create the Excel graphs, I’m using the following formula from the Stata pdf documentation (pg 364)…

[See formula in attachment]

…and inserting the unlogged sigma (1.19) and constant (1.83) from output produced by the streg command above to generate the survival estimates (S). Then I’m converting the survival to a cumulative hazard (-LN[S]), and then adjusting the hazard ratios so they aren’t cumulative. Which (zoomed in on the first 30 days) produces:

[See excel image in attachment]

My question is: is it possible to reproduce this graph exactly in Excel so the hazard ratios on the y axes align? And if so, is the error in the formula I used, or is something else going on? I would feel better if I could fully replicate the graph from Stata.

Thank you in advance for any advice! (And I hope the attachment is visible - I was having trouble uploading some of the images as photos, so feel free to let me know of any problems viewing it.)
Megan

[Nick Cox]

MS Word documents are not universally accessible. Many Stata users, even those using Windows, don't use it or have access to it.

I can only see one link here, to a Word document (which I can't read).

The best way to refer to Stata documentation is to give a precise link to the .pdf concerned.

The best way to give a Stata graph is to reproduce it in Stata as .png and then post that as an attachment.

On your substantive question, I am not sure how many people familiar with the survival commands in Stata are fluent in MS Excel too, but you only need one such person.

[Megan Denver]

Thanks, Nick - let me give this a second try. Uploading .png images of both graphs instead, and here's the Stata documentation link (pg 6): http://www.stata.com/manuals13/ststreg.pdf

Megan

[Nick Cox]

The photos are both visible to me. My recommendation was to upload them as attachments (clipboard icon), not as images, but they are readable.

[Joseph Coveney]

Can you just use predict double my_hazard, hazard after fitting the model and then export the predictions (along with analysis time) to a file for reading into Excel? Or, could you include a variable in the model to distinguish the groups whose hazard functions' levels you want to compare? Perhaps then you could use the at1() at2() . . . at10() option of the stcurve , hazard postestimation command to compare the hazard functions' levels on one Stata graph in lieu of trying to export predictions and assemble the plots in Excel.

[Megan Denver]

Thanks, Joseph - ultimately I'll probably use Stata for this, and the predict command you suggest above is helpful. I was just hoping to build intuition about what Stata is doing by manually replicating it in Excel (since discussing the shape parameter in detail is a big piece of my project). If anyone has suggestions about what I might be doing wrong in the above graphs (misinterpreting the formula, etc.), any advice is welcome. Thanks!

[Megan Denver]

On the off chance anyone else wants to play with lognormal graphs in excel, after some more digging and trial/error this week I found that the LOGNORM.DIST function in Excel replicates Stata's stcurve really well. (In the help documentation, Excel writes the formula as LOGNORM.DIST(x,µ,σ) = NORM.S.DIST(ln(x)-µ / σ).) After running your streg command, you can just plug in the mean and sigma values from the Stata output for the unit of time x that you're interested in (and that you used in your stset command earlier), and the graphs look the same. Doubtful it will be useful to many others, but wanted to pass it along just in case!