BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Obsidian | Level 7

## How to replace zero with previous and next values

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tourmaline | Level 20

## Re: How to replace zero with previous and next values

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;``````
5 REPLIES 5
Tourmaline | Level 20

## Re: How to replace zero with previous and next values

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;``````
Obsidian | Level 7

## Re: How to replace zero with previous and next values

Thank you very much draycut. It works well.
Tourmaline | Level 20

## Re: How to replace zero with previous and next values

Glad you found your answer 🙂

Amethyst | Level 16

## Re: How to replace zero with previous and next values

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;``````
Thanks,
Jag
Obsidian | Level 7

## Re: How to replace zero with previous and next values

Thank a lot Jagadishkatam.
Discussion stats
• 5 replies
• 1115 views
• 4 likes
• 3 in conversation