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;
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.
/* 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;
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.