Hi @Khalidbo1
Completetypes options, etc. will work only if the modality occurs at least once in your input dataset.
For example, if you have no record for month = 11 whatever the country or the year, it will not be added to the output dataset.
Here is an approach to avoid this trouble.
Hope this helps!
Best,
data have;
input country $ year month sum;
cards;
country1 2018 1 2
country1 2018 2 1
country1 2018 3 9
country1 2018 4 4
country1 2018 6 2
country1 2019 1 2
country1 2019 2 1
country1 2019 3 9
country2 2018 2 9
country2 2018 11 4
country2 2018 2 9
country2 2018 4 9
country2 2018 10 4
country2 2018 11 4
country2 2019 4 9
country2 2019 10 4
country2 2019 11 4
;
run;
data want;
if _n_=1 then do;
declare hash h (dataset:"have (rename=(sum=_sum))");
h.definekey ("country", "year", "month");
h.definedata ("_sum");
h.definedone();
end;
set have;
by country;
if first.country then do;
do year=2017 to 2019; /* <--- specify the years */
do month = 1 to 12;
if h.find()=0 then sum = _sum;
else sum=0;
output;
end;
end;
end;
drop _sum;
run;
... View more
