topic Re: How to replace zero with previous and next values in SAS Programming

How to replace zero with previous and next values

fongdo — Wed, 08 May 2019 07:10:14 GMT

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

Re: How to replace zero with previous and next values

PeterClemmensen — Wed, 08 May 2019 07:42:02 GMT

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;

Re: How to replace zero with previous and next values

Jagadishkatam — Wed, 08 May 2019 08:42:01 GMT

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;

Re: How to replace zero with previous and next values

fongdo — Thu, 09 May 2019 06:39:17 GMT

Thank you very much draycut. It works well.

Re: How to replace zero with previous and next values

fongdo — Thu, 09 May 2019 06:42:21 GMT

Thank a lot Jagadishkatam.

Re: How to replace zero with previous and next values

PeterClemmensen — Thu, 09 May 2019 07:14:04 GMT

Glad you found your answer 🙂