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
If you want solution of proc expand , You could post it at SAS Forecasting and Econometrics Forum.
The following is data step solution.
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;
If you want solution of proc expand , You could post it at SAS Forecasting and Econometrics Forum.
The following is data step solution.
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;
Many thanks.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.