add zero for missing value

[hk2013]

I have a data set that has ID, Metric, score and month but i am noticing some the ids data for month here or there for certain metrics 

what i have: 

id    Metric  month        score

1       a          1               .9

1      b           1               .9

1     c            1              .8

1    d             1            .8

1    a             2             .7

1    c             2            .8

1   d             2              .8

1   b             3             .9

1   c             3              .6

1  d             3              .6

out put i want 

id    Metric  month        score

1       a          1               .9

1      b           1               .9

1     c            1              .8

1     d             1            .8

1     a             2             .7

1     b            2              0

1     c             2            .8

1    d             2              .8

1     a            3              0

1    b             3             .9

1    c             3              .6

1   d             3              .6

[TomKari]

Easy-peasy.

Create a table of all the possible ID/Metric/Month combinations, and then left-join it.

Tom

data Have;
	length ID 8 Metric $1 Month 8 Score 8;
	input ID Metric Month Score;
	cards;
1 a 1 .9
1 b 1 .9
1 c 1 .8
1 d 1 .8
1 a 2 .7
1 c 2 .8
1 d 2 .8
1 b 3 .9
1 c 3 .6
1 d 3 .6
run;

data AllRecs;
	length Metric $1;
	Score = 0;

	do ID = 1 to 10;
		do Metric = "a", "b", "c", "d";
			do Month = 1 to 12;
				output;
			end;
		end;
	end;
run;

proc sql noprint;
	create table want as
		select a.ID, a.Metric, a.Month,
			case 
				when h.Score is missing then 0 
				else h.Score 
			end
		from AllRecs a left join Have h on a.ID = h.ID and a.Metric = h.Metric and a.Month = h.Month;
quit;

[novinosrin]

data have;
input id    Metric $  month        score;
cards;
1       a          1               .9
1      b           1               .9
1     c            1              .8
1    d             1            .8
1    a             2             .7
1    c             2            .8
1   d             2              .8
1   b             3             .9
1   c             3              .6
1  d             3              .6
;
proc sort data=have out=temp(keep=id month) nodupkey;
by id month;

data want;
if _n_=1 then do;
   dcl hash H (dataset:'have',multidata:'y',ordered: "A") ;
   h.definekey  ("id",'month','metric') ;
   h.definedata ('score') ;
   h.definedone () ;
end;
set temp;
by id month;
do _t=97 to 100;
metric=byte(_t);
if h.find() ne 0 then score=0;
output;
end;
drop _:;
run;

[Astounding]

SQL might be the easiest way to create a shell with the complete structure in it:

proc sql noprint;

create table id_list as select distinct id from have;

create table metric_list as select distinct metric from have;

create table month_list as select distinct month from have;

create table all_values as select * from id_list, metric_list month_list

   orderby id metric month;

quit;

Then fill in the blanks:

data want;

merge all_values have (in=have_it);

by id metric month;

if have_it=0 then score = 0;

run;