proc expand

[Kona]

Hi,

I am trying to expand a data set to include missing time points.  The data is:

data have;

input id year x;

cards;

1 2001 1

1 2003 1

2 2002 1

2 2005 1

3 2002 1

3 2002 1

4 2000 1

4 2001 1

;

I am trying to expand to get the full time series for each id (not the same time series.  Note x is just a place holder as all I care about is the id and year.  The output I am trying to get is:

id year x;

1 2001 1

1 2002 .

1 2003 1

2 2002 1

2 2003 .

2 2004 .

2 2005 1

3 2002 1

4 2000 1

4 2001 1

And I have been trying the following:

proc expand data=have out=want from=daily method=none extrapolate;

by id;

id year;

run;

which gives:

1	1	2001	1
2	1	2002	.
3	1	2003	1
4	2	2002	1
5	2	2003	.
6	2	2004	.
7	2	2005	1
8	4	2000	1
9	4	2001	1

The issue I run into is I lose id=3 as it has the same start and stop year.  I also tried to use proc sql but haven't been able too figure it out.  Apologies if this is a repeat but I couldn't find code that worked.

Thanks in advance for the help,

-Kona

[Ksharp · Accepted solution]

If you want solution of proc expand , You could post it at SAS Forecasting and Econometrics  Forum.

The following is data step solution.

Code: Program

data have;
input id year x;
cards;
1 2001 1
1 2003 1
2 2002 1
2 2005 1
3 2002 1
3 2002 1
4 2000 1
4 2001 1
;
run;

data want;
 merge have have(firstobs=2 keep=id year rename=(year=_y id=_id));
 output;
 if id=_id then do;
  do i=year+1 to _y-1;
   year=i;x=.;output;
  end;
 end;
 drop i _:;
run;

[Kona]

Many thanks.