How to replace zero with previous and next values

[fongdo]

I would like to replace zero with the previous values by FirmID. However, if there is no previous values, it will be replaced with the next values. Please see a sample below:

Thank you very much

HAVE

FirmID            DATE                          DEBT              EQUITY

1                      01/01/2000                  0                      8

1                      02/04/2000                  0                      0

1                      03/04/2000                  4                      0

1                      04/04/2000                  5                      0

1                      05/04/2000                  6                      7

1                      06/04/2000                  0                      7

1                      07/04/2000                  0                      7

2                      01/01/2000                  3                      0

2                      02/04/2000                  3                      0

2                      03/04/2000                  4                      6

2                      04/04/2000                  5                      6

2                      05/04/2000                  6                      7

2                      06/04/2000                  6                      8

2                      07/04/2000                  0                      9

WANT

FirmID            DATE                          DEBT              EQUITY

1                      01/01/2000                  4                      8

1                      02/04/2000                  4                      8

1                      03/04/2000                  4                      8

1                      04/04/2000                  5                      8

1                      05/04/2000                  6                      7

1                      06/04/2000                  6                      7

1                      07/04/2000                  6                      7

2                      01/01/2000                  3                      6

2                      02/04/2000                  3                      6

2                      03/04/2000                  4                      6

2                      04/04/2000                  5                      6

2                      05/04/2000                  6                      7

2                      06/04/2000                  6                      8

2                      07/04/2000                  6                      9

[PeterClemmensen · Accepted solution]

There is probably a smarter way, but here, I consider 0 as missing values. Then I simply fill down first, and 'up' next. Let me know if this works for you 

data have;
input FirmID $ DATE:ddmmyy10. DEBT EQUITY;
format DATE ddmmyy10.;
datalines;
1 01/01/2000 0 8
1 02/04/2000 0 0
1 03/04/2000 4 0
1 04/04/2000 5 0
1 05/04/2000 6 7
1 06/04/2000 0 7
1 07/04/2000 0 7
2 01/01/2000 3 0
2 02/04/2000 3 0
2 03/04/2000 4 6
2 04/04/2000 5 6
2 05/04/2000 6 7
2 06/04/2000 6 8
2 07/04/2000 0 9
;

data temp1;
   set have;
   if DEBT=0 then DEBT=.;
   if EQUITY=0 then EQUITY=.;
run;

data temp2;
   update temp1(obs=0) temp1;
   by FirmID;
   output;
run;

proc sort data=temp2;
   by FirmID descending date;
run;

data temp3;
   update temp2(obs=0) temp2;
   by FirmID;
   output;
run;

proc sort data=temp3 out=want;
   by FirmID date;
run;

proc datasets lib=work nolist;
   delete Temp:;
run;quit;

[fongdo]

Thank you very much draycut. It works well.

[PeterClemmensen]

Glad you found your answer 🙂

[Jagadishkatam]

Alternatively

data have;
input FirmID $ DATE:ddmmyy10. DEBT EQUITY;
if DEBT=0 then DEBT=.;
if EQUITY=0 then EQUITY=.;
format DATE ddmmyy10.;
datalines;
1 01/01/2000 0 8
1 02/04/2000 0 0
1 03/04/2000 4 0
1 04/04/2000 5 0
1 05/04/2000 6 7
1 06/04/2000 0 7
1 07/04/2000 0 7
2 01/01/2000 3 0
2 02/04/2000 3 0
2 03/04/2000 4 6
2 04/04/2000 5 6
2 05/04/2000 6 7
2 06/04/2000 6 8
2 07/04/2000 0 9
;

data want;
set have;
by FirmID;
retain DEBT_ EQUITY_;
if first.FirmID then do;
DEBT_=.;
EQUITY_=.;
end;
if DEBT ne . then DEBT_=DEBT;
if EQUITY ne . then EQUITY_=EQUITY;
drop DEBT EQUITY;
run;

proc sort data=want;
by FirmID descending DATE;
run;

data want2;
set want;
by FirmID descending DATE;
retain DEBT EQUITY;
if first.FirmID then do;
DEBT=.;
EQUITY=.;
end;
if DEBT_ ne . then DEBT=DEBT_;
if EQUITY_ ne . then EQUITY=EQUITY_;
drop DEBT_ EQUITY_;
run;

proc sort data=want2;
by FirmID  DATE;
run;

[fongdo]

Thank a lot Jagadishkatam.