topic Re: Filling Data Between Observation Dates and Destinations in SAS Programming

Filling Data Between Observation Dates and Destinations

BCNAV — Fri, 11 May 2018 15:05:20 GMT

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

Re: Filling Data Between Observation Dates and Destinations

novinosrin — Fri, 11 May 2018 15:41:36 GMT

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;

Re: Filling Data Between Observation Dates and Destinations

BCNAV — Fri, 11 May 2018 20:06:01 GMT

perfect! Thanks!

Re: Filling Data Between Observation Dates and Destinations

Ksharp — Sat, 12 May 2018 11:09:33 GMT

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;