Inserting row with retained data if missing from set

[altatunc]

 data WORK.TestData;
   infile datalines truncover;
   input ID:$8. Type:$1. Color:$32. Quarter:32. Score:32.;
 datalines;
 A11 1 G 1 99
 A11 1 G 2 98
 A11 1 G 3 100
 A11 2 B 1 97
 A11 2 B 2 90
 A11 2 B 3 90
 A12 1 G 1 99
 A12 1 G 2 88
 A12 1 G 3 48
 A12 1 G 4 59
 A13 1 G 1 99
 A13 1 G 2 88
 A13 1 G 3 48
;;;;;;;;;;

So, in this data or data like it, i need a Quarter 4 represented for each person (unique ID) and each "type".  Even if the score would be empty I need to insert a row with a Quarter 4 and the above data to match it (again score as missing is fine).  This was problematic with creating an extra data set and merging back in because i had 2 "types" for one person in the data set. A PROC SQL would only merge in the top most Type of the Q4 data.  A retain statement was able to copy back in the data, but again, only for the first Type if more than one was present.

Thoughts are most appreciated.

[novinosrin · Accepted solution]

 data WORK.TestData;
   infile datalines truncover;
   input ID:$8. Type:$1. Color:$32. Quarter:32. Score:32.;
 datalines;
 A11 1 G 1 99
 A11 1 G 2 98
 A11 1 G 3 100
 A11 2 B 1 97
 A11 2 B 2 90
 A11 2 B 3 90
 A12 1 G 1 99
 A12 1 G 2 88
 A12 1 G 3 48
 A12 1 G 4 59
 A13 1 G 1 99
 A13 1 G 2 88
 A13 1 G 3 48
;;;;;;;;;;

data want;
 do until(last.type);
  set testdata;
  by id type;
  output;
 end;
 if Quarter ne  4 then do Quarter=Quarter+1 to 4;
 call missing(score);
 output;
 end;
run;

[novinosrin]

Or SPARSE in proc freq is a beauty

data WORK.TestData;
   infile datalines truncover;
   input ID:$8. Type:$1. Color:$32. Quarter:32. Score:32.;
 datalines;
 A11 1 G 1 99
 A11 1 G 2 98
 A11 1 G 3 100
 A11 2 B 1 97
 A11 2 B 2 90
 A11 2 B 3 90
 A12 1 G 1 99
 A12 1 G 2 88
 A12 1 G 3 48
 A12 1 G 4 59
 A13 1 G 1 99
 A13 1 G 2 88
 A13 1 G 3 48
;;;;;;;;;;

/*Get all combinations*/
proc freq data=testdata noprint;
tables id*type*quarter/out=temp(keep=id type quarter) sparse;
run;
/*Merge with SQL*/
proc sql;
create table want as
select a.*,b.score
from temp a left join testdata b
on a.id=b.id and a.type=b.type and a.quarter=b.quarter
order by a.id,a.type,a.quarter;
quit;
/*or Merge with datastep*/
data want;
merge temp testdata;
by id type quarter;
run;

[altatunc]

Thank you!  This seems to work with the test data and i am sure it will with the real data.

[r_behata]

 data WORK.TestData;
   infile datalines truncover;
   input ID:$8. Type:$1. Color:$32. Quarter:32. Score:32.;
 datalines;
 A11 1 G 1 99
 A11 1 G 2 98
 A11 1 G 3 100
 A11 2 B 1 97
 A11 2 B 2 90
 A11 2 B 3 90
 A12 1 G 1 99
 A12 1 G 2 88
 A12 1 G 3 48
 A12 1 G 4 59
 A13 1 G 1 99
 A13 1 G 2 88
 A13 1 G 3 48
;;;;;;;;;;
run;


data want;
set WORK.TestData;
by Id type;
output;

if last.type then do;
	do while(quarter lt 4);
			Quarter+1;
			output;
		end;
end;

run;

[Tom]

There is no problem with making a template dataset and merging it back.

Perhaps you just haven't explained what the rules for the template dataset is?

proc sql ;
create table skeleton as
  select distinct id,type,color,4 as Quarter
  from testdata
  order by id,type,color,quarter
;
quit;

data want;
  merge skeleton testdata;
  by id type color quarter;
run;

[altatunc]

Thank you! I am not sure what my merge had wrong but I suspect it was because i used a sql full join which only choose the first option and moved on.