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

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
PeterClemmensen
Tourmaline | Level 20

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;

View solution in original post

5 REPLIES 5
PeterClemmensen
Tourmaline | Level 20

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
Obsidian | Level 7
Thank you very much draycut. It works well.
Jagadishkatam
Amethyst | Level 16

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
fongdo
Obsidian | Level 7
Thank a lot Jagadishkatam.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1084 views
  • 4 likes
  • 3 in conversation