Hello! Any help here is greatly appreciated.

I have written a program that takes summarized values from categorized dummy variables (using tab, gen(X)), organizes them into a matrix, and then drops them in to excel for reporting purposes. The issue I am having is that if there is a category that is not populated in the data, the program does not recognize the necessity to skip that set of observations (or at least drop zeros). Instead it condenses the matrix and I get reporting that is misaligned. What I want is a way where the program recognizes the missing category, drops zeros in for that category, and then continues onto the next category.

Here is some dummy data, note that the question var is based on has a 1-5 scale, and there are no observations for category 1:

Here is the program that I wrote:

And here is the macro to the program:

When I run the program for var, the zeros I want at the beginning of the matrix show up at the end. This has happened even when I swap the if and else statements (and set the criteria to c(rc) == 0). I am not sure how to address or fix this to make it work in the way intended.

Lastly, this is my firs time on StataList, so any feedback and/or pointers are greatly apprecaited!

I have written a program that takes summarized values from categorized dummy variables (using tab, gen(X)), organizes them into a matrix, and then drops them in to excel for reporting purposes. The issue I am having is that if there is a category that is not populated in the data, the program does not recognize the necessity to skip that set of observations (or at least drop zeros). Instead it condenses the matrix and I get reporting that is misaligned. What I want is a way where the program recognizes the missing category, drops zeros in for that category, and then continues onto the next category.

Here is some dummy data, note that the question var is based on has a 1-5 scale, and there are no observations for category 1:

obs | var |

1 | |

2 | 3 |

3 | 4 |

4 | 5 |

5 | 2 |

6 | 3 |

7 | 4 |

8 | 2 |

9 | 4 |

10 | |

11 | 5 |

12 | 4 |

13 | 2 |

14 | 3 |

15 | 4 |

16 | 2 |

17 | 3 |

18 | |

19 | 5 |

20 | 2 |

Code:

program define scale1 // for questions that use 1-5 scale * Row Header quietly putexcel A`2'="`3'", left vcenter txtwrap * Dataframe mat `1'_F1 = J(1,10,.) tab `1', gen(`1'_tab) local c = 1 forval t = 1/5 { local k = `c' capture sum `1'_tab`t' if c(rc) != 0 { mat `1'_F1[1,`k'] = 0 local k = `k' + 1 mat `1'_F1[1,`k'] = 0 } else { return list mat `1'_F1[1,`k'] = r(sum) local k = `k' + 1 mat `1'_F1[1,`k'] = round(100*r(mean),0.001) } local c = `c' + 2 } mat list `1'_F1 quietly putexcel B`2'=matrix(`1'_F1), nformat(number_sep) hcenter vcenter mat drop `1'_F1 drop `1'_tab* end

Code:

*program(0) groupvar(1) row(2) label(3) scale1 var 5 "Row Label"

Lastly, this is my firs time on StataList, so any feedback and/or pointers are greatly apprecaited!

## Comment