Using putexcel in a loop- help needed populating the full table produced

[Farah Yehia]

Hi, would appreciate your help figuring out the missing piece to the below code. I aim to produce and populate the below table in excel. I have used a loop for the variable "Drug Identifier" present in the column of the table repeated 3 times ("unrestricted", "protected", and "unprotected").

Every cell should be populated by a number that is produced at the end of each loop ("percent_coverage").

However, my problem is that the current code I have only populates the first row of the table, and it populates it with the "percent_coverage" which corresponds to the last variable in my variable list (which happens to be "firstfourdigits"), not the first ("ndc").

How can I modify the code to populate all rows and columns with the correct corresponding numbers?

Thank you.

Sample table to be produced in excel:

Drug Identifier/Dataset	Unrestricted	Protected	Unprotected
ndc
generid
Firstwelvedigits
Gennme
firstfourdigits

My code:

qui {
putexcel set results, modify
putexcel A1 = "Identifier/Dataset"
putexcel A2 = "NDC"
putexcel A3 = "generid"
putexcel A4 = "firstwelvedigits"
putexcel A5 = "gennme"
putexcel A6 = "firstfourdigits"
putexcel B1 = "Unrestricted"
putexcel C1 = "Protected"
putexcel D1 = "Unprotected"
*unrestricted
foreach var in ndc generid firsttwelvedigits gennme firstfourdigits {
capture drop percent_coverage
capture drop `var'counter
capture drop `var'formcounter
capture drop formcount_by`var'
bysort `var': generate `var'counter=(_n==1)
bysort `var' formulary_id: gen `var'formcounter=(_n ==1)
bysort `var': egen formcount_by`var'= sum(`var'formcounter)
count if `var'counter==1
local totalcount = r(N)
count if formcount_by`var'== 57 & `var'counter==1
local count =r(N)
no display "unrestricted:" "`var' ; " `count' "; " `totalcount' "; " %5.2f `count' / `totalcount' * 100 "% "
gen percent_coverage= `count' / `totalcount' * 100
summarize percent_coverage
putexcel B2=`r(mean)', nformat(percent_d2)
}
*protected
foreach var in ndc generid firsttwelvedigits gennme firstfourdigits {
capture drop percent_coverage
capture drop `var'counter
capture drop `var'formcounter
capture drop formcount_by`var'
bysort `var': generate `var'counter=(_n==1) if protected==1
bysort `var' formulary_id: gen `var'formcounter=(_n ==1) if protected==1
bysort `var': egen formcount_by`var'= sum(`var'formcounter) if protected==1
count if `var'counter==1 & protected==1
local totalcount = r(N)
count if formcount_by`var'== 57 & `var'counter==1 & protected==1
local count =r(N)
no display "protected:" "`var' ; " `count' "; " `totalcount' "; " %5.2f `count' / `totalcount' * 100 "% "
gen percent_coverage= `count' / `totalcount'
summarize percent_coverage
putexcel C2=`r(mean)', nformat(percent)
}
*unprotected
foreach var in ndc generid firsttwelvedigits gennme firstfourdigits {
capture drop percent_coverage
capture drop `var'counter
capture drop `var'formcounter
capture drop formcount_by`var'
bysort `var': generate `var'counter=(_n==1) if protected==0
bysort `var' formulary_id: gen `var'formcounter=(_n ==1) if protected==0
bysort `var': egen formcount_by`var'= sum(`var'formcounter) if protected==0
count if `var'counter==1 & protected==0
local totalcount = r(N)
count if formcount_by`var'== 57 & `var'counter==1 & protected==0
local count =r(N)
no display "unprotected:" "`var' ; " `count' "; " `totalcount' "; " %5.2f `count' / `totalcount' * 100 "% "
gen percent_coverage= `count' / `totalcount'
summarize percent_coverage
putexcel D2=`r(mean)', nformat(percent)
}
}

[Clyde Schechter]

The code that you, yourself, have put in bold face is precisely the source of the problem. While you go through the loop and iterate over ndc through first four digits, at the bottom of the loop, each time you are writing into the second row of your spreadsheet, so you just repeatedly over-write the results, and only the last ones (firstfourdigits) survive.

You need to add a counter to the loops that increases the row number each time through. So something like this:

[code]
local rownum 2
foreach var in ndc generid firsttwelvedigits gennme firstfourdigits {
capture drop percent_coverage
capture drop `var'counter
capture drop `var'formcounter
capture drop formcount_by`var'
bysort `var': generate `var'counter=(_n==1)
bysort `var' formulary_id: gen `var'formcounter=(_n ==1)
bysort `var': egen formcount_by`var'= sum(`var'formcounter)
count if `var'counter==1
local totalcount = r(N)
count if formcount_by`var'== 57 & `var'counter==1
local count =r(N)
no display "unrestricted:" "`var' ; " `count' "; " `totalcount' "; " %5.2f `count' / `totalcount' * 100 "% "
gen percent_coverage= `count' / `totalcount' * 100
summarize percent_coverage
putexcel B`rownum' =`r(mean)', nformat(percent_d2)
local ++rownum
}
[code]

Note: Not tested.

Make similar changes to the other loops.

In the future, please use code delimiters when posting code so that indentation is preserved. (If your original code does not have any indentation, you are well advised to form the habit of using indentation: it makes code much easier to understand, debug, maintain, and modify.) If you are not familiar with code delimiters, please read the Forum FAQ with special attention to #12.

[Farah Yehia]

Thank you, this worked perfectly.

Thanks for the tip about delimiters too, will use them next time.