topic Add extra year rows in panel data in SAS Programming

Add extra year rows in panel data

dapenDaniel — Wed, 08 Apr 2020 21:45:09 GMT

Hi,

I have a dataset like below.

data have;
input firmID $	Year	Value;
datalines;
A	1995	1
A	1996	1
A	1997	1
A	1998	1
B	1997	2
B	1998	2
B	1999	2
;
run;

I would like to add one more year for each firm while keeping the value the same. The expected output is below.

data have;
input firmID $	Year	Value;
datalines;
A	1995	1
A	1996	1
A	1997	1
A	1998	1
A   1999    1
B	1997	2
B	1998	2
B	1999	2
B   2000    2
;
run;

Re: Add extra year rows in panel data

DavePrinsloo — Wed, 08 Apr 2020 21:55:57 GMT

/* untested */

proc sort data=have;
by firmid year;
run;

data want;
set have;
by firmid year;
output:
if last .firmid then do;
   year=year+1;
   output;
end;
run;

data have; input firmID $ Year Value;

Re: Add extra year rows in panel data

ballardw — Wed, 08 Apr 2020 21:56:04 GMT

One way:

data have;
input firmID $	Year	Value;
datalines;
A	1995	1
A	1996	1
A	1997	1
A	1998	1
B	1997	2
B	1998	2
B	1999	2
;
run;

data want;
  set have;
  by firmid;
  if last.firmid then do;
   output;
   year=year+1;
   output;
 end;
 else output;
run;

Assumes the data is sorted by firmid and year. If not actually sorted by Firmid use:

by notsorted firmid;

but all of the firmid values better be one after the other.

An explicit OUTPUT statement writes to the data set when encountered and overrides the default "at the bottom of the data step". So you need the Else Output to get the unmodified output. The first output inside the do block writes the last record, then modifies the variable and writes the modified record.

Re: Add extra year rows in panel data

dapenDaniel — Thu, 09 Apr 2020 04:02:49 GMT

Thanks!!!