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!
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() } //