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
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;
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;
perfect! Thanks!
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.