Using arrays is generally the way to do these things. For the first step, I would use the COALESCE function: data the_matrix_query; set the_matrix_query_withnull; array years(*) 8 year:; do _N_=1 to dim(years); years(_N_)=coalesce(years(_N_),0); end; run; Of course, you may want to make the array data explicit (year2009-year2017) instead of year:, if you have other variable names beginning with "year". The second step can be done in a similar way, except that you cannot use coalesce, but must use if...then. The last two steps can be put into a single step: data makevector; set the_matrix_query; where week<53; array years(2009:2017) 8 year2009-year2017; do year=2009 to 2017; count=years(year); output; end; keep week year count; end; I am not sure why you did not put the week<53 condition on 2014. Is that because there was no week 53 in 2014, or because you really want week 53 included for 2014 specifically? In the latter case, you will have to change the code a bit, e.g.: data makevector; set the_matrix_query; array years(2009:2017) 8 year2009-year2017; if week<53 then do year=2009 to 2017; count=years(year); output; end; else do; count=year2014; year=2014; output; end; keep week year count; end;
... View more