Filling Data Between Observation Dates and Destinations

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 92
Accepted Solution

Filling Data Between Observation Dates and Destinations

Sorry for the weird title. A data supplier is sending us data that looks as follows:

 

Date      depart   dest   Count   Direction

20180401  MBPV     LIPE   1       1

                   LSGG   1       1

                   LSZH   1       1

          MDCY     EDDF   4       2

                   EIDW   1       1

          MDLR     EDDF   1       1

                   EDDK   1       1

 

and so on...we need to get it into SAS so that the blanks you see above are filled in until a new depart occurs, with the date populated the whole way down. It would end up creating:

 

Date      depart   dest   Count   Direction

20180401  MBPV     LIPE   1       1

20180401  MBPV     LSGG   1       1

20180401  MBPV     LSZH   1       1

20180401  MDCY     EDDF   4       2

20180401  MDCY     EIDW   1       1

20180401  MDLR     EDDF   1       1

20180401  MDLR     EDDK   1       1

 

 

Is this possible in a SAS datastep? If you want to download the original 192kb SAS dataset you can get it:

 

https://drive.google.com/file/d/1ugq_Oh6vW1spPnRaTAx3ahFdeR5E0yMX/view?usp=sharing

 

thanks

-Bill


Accepted Solutions
Solution
‎05-11-2018 04:06 PM
PROC Star
Posts: 1,845

Re: Filling Data Between Observation Dates and Destinations

data want;
set have;
retain _d __d;
if not missing(depart) then  _d=depart;
else depart=_d;
if not missing(date) then __d=date;
else date=__d;
drop _:;
run;

View solution in original post


All Replies
Solution
‎05-11-2018 04:06 PM
PROC Star
Posts: 1,845

Re: Filling Data Between Observation Dates and Destinations

data want;
set have;
retain _d __d;
if not missing(depart) then  _d=depart;
else depart=_d;
if not missing(date) then __d=date;
else date=__d;
drop _:;
run;
Frequent Contributor
Posts: 92

Re: Filling Data Between Observation Dates and Destinations

Posted in reply to novinosrin

perfect!  Thanks!

Super User
Posts: 10,787

Re: Filling Data Between Observation Dates and Destinations

data have;
input (Date      depart   dest   Count   Direction) ($);
id=1;
cards;
20180401  MBPV     LIPE   1       1
   .        .        LSGG   1       1
   .         .       LSZH   1       1
   .       MDCY     EDDF   4       2
   .         .       EIDW   1       1
   .       MDLR     EDDF   1       1
   .        .        EDDK   1       1
;
run;
data want;
 update have(obs=0) have;
 by id;
 output;
 drop id;
run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 144 views
  • 1 like
  • 3 in conversation