No announcement yet.
  • Filter
  • Time
  • Show
Clear All
new posts

  • How do I force zeros or blank values for missing observations in a loop.

    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:
    obs var
    2 3
    3 4
    4 5
    5 2
    6 3
    7 4
    8 2
    9 4
    11 5
    12 4
    13 2
    14 3
    15 4
    16 2
    17 3
    19 5
    20 2
    Here is the program that I wrote:
    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*
    And here is the macro to the program:
    *program(0)        groupvar(1)        row(2)    label(3)
    scale1                 var                      5            "Row Label"
    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!
    Last edited by Jup Gill; 20 Apr 2021, 08:56.

  • #2
    I figured it out! It was not the if and else statements in my program, but the use of tab, gen(x), as it wasn't taking into account the categorical value when creating the dummy suffix. Therefore, I replace it with another loop to force-create a dummy variable even if data for that category doesn't exist. It does what I want it to do now.