Proc SQL: Filling in missing rows

[BruceBrad]

I have a dataset with observations for each year for each person. Some years are missing. I want to expand the file to have all years for each person (with missing values for the missing years). Can this be done with an SQL statement. The code below doesn't work.

data one;
input id year var;
cards;
1 2001 3
1 2002 5
1 2003 7
2 2002 4
2 2003 6
;
run;

data years;
input year;
cards;
2001
2002
2003
;
run;

data desired;
input id year var;
cards;
1 2001 3
1 2002 5
1 2003 7
2 2001 .
2 2002 4
2 2003 6
;

/* this doesn't add in the missing row */;
proc sql;
  create table comb as
  select years.year, one.id, one.var from
  years left join one
  on one.year = years.year
  order by id,year ;
quit;
run;

[LinusH · Accepted solution]

One way is to make a cartesian product between id and years firs, then do the left join from your original table.

proc sql;
  create table comb as
  select id_years.year, id_years.id, one.var from
  (select id, years.year 
	from (select distinct id from one), years) id_years
left join one
  on one.id = id_years.id and 
	one.year = id_years.year
  order by id,year ;
quit;

[BruceBrad]

Thanks. Very helpful.