Help using Base SAS procedures

proc expand

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

proc expand

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:

1120011
212002.
3120031
4220021
522003.
622004.
7220051
8420001
9420011

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


Accepted Solutions
Solution
‎08-03-2015 08:21 AM
Super User
Posts: 10,035

Re: proc expand

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;

View solution in original post


All Replies
Solution
‎08-03-2015 08:21 AM
Super User
Posts: 10,035

Re: proc expand

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;
New Contributor
Posts: 2

Re: proc expand

Many thanks.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 309 views
  • 3 likes
  • 2 in conversation