Data Conversion From Excel to STATA

[Luis Mijares]

I have the following excel data set (sorry if image is blurry) I want to covert this excel data to a long data format on stata. I am not sure how to do this, I I initially uploaded the excel file using import excel and listing the cells I want but the import excel command generated the STATA format seen below. One specific problem is that the excel file lists average drinking by mean, upci,lci for each borough from the years 2003-2019. STATA is not recognizing each year as a different variable so it creates a variable mean, upcl,lcl for the year 2003 then defaults to using the name of the column. I hope this makes sense sorry for any confusion in my writing English is not my native language

[Clyde Schechter]

Well, you are asking for something that Stata cannot do. You don't show the actual -import excel- command you used, but from what you show, I infer that you specified a -cellrange- option that began the import from cell A2. That entire first row of the Excel file is not coming in. Moreover, if you remove that option so that importation begins in row 1, then all of the variables will come in as strings and the first observation in the Stata data set will be full of things like "Mean"

It is easy for our human eyes and brains to perceive the first row of that spreadsheet as being intimately bound up with the second row and, jointly, forming column headers with the year from row 1 distributing over the names of the statistics in row 2. But Stata does not have that capability. I'm afraid you will have to just begin with what you already have in Stata and then -rename- the variables.