Announcement

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

  • Replacing missing with string in excel file using mata

    Hi all,
    i create some excel tables in stata, using putexcel and mata xl() command, but since i do it for several different folders it is all in loops over folders. Problem is that i want in each table to replace missing with string value NA, and what i understand it can be done with mata command xl() put_string option, but since i would loop it over each cell, i have mata loop in stata loop and it doesnt work. Then i wanted to create function in mata that will handle it, but i cant get it done as xl() uses structures and i in that point i get lost and got errors. What am I doing wrong in the example i will put below?. Additionally, if there is some completely different and simpler way to do the same thing i would appreciate advce!

    Code:
    mata: mata clear
    mata
    void add_na(real scalar a, real scalar b) {
    for (r=a; r<=b; r++) {
       for (c=1; c<=3; c++) {
       struct mystruct scalar e
       cc = c*2
       rows = (r,r)
       cols = (cc,cc)
       rows
       cols
       cells = e.get_number(rows,cols)
       if (cells == .) {
            e.put_string(r,cc, "NA")
       }
    }
    }
    }
    end
    
    foreach f in folder1 folder2 ... {
    
    cd "each folder1/2/3... where i create xlsx"
    //here i put numbers into table etc, with "putexcel" .... using the same example.xlsx
    
    // here i use some xl() things for which i do not need loop
    
    mata b = xl()
    mata b.load_book("example.xlsx")
    mata b.set_column_width(2, 2, 15)
    mata b.set_column_width(3, 3, 10) //etc
    
    // here i would like to replace all missings with string NA
    mata ad_na()
    
    }
    //
    Attached Files
    Last edited by Neko Novi; 29 Nov 2017, 15:09.
Working...
X