hi,
I have this kind of variable (from 3 observation only the first one has value):
DATE
12 Nov 2013
13 Nov 2013
14 Nov 2013
and I want to make it like this:
DATE
12 Nov 2013
12 Nov 2013
12 Nov 2013
13 Nov 2013
13 Nov 2013
13 Nov 2013
14 Nov 2013
14 Nov 2013
14 Nov 2013
Hi,
data have;
a=1; date="12 Nov 2013"; output;
a=2; date=""; output;
a=3; date=""; output;
a=1; date="13 nov 2013"; output;
run;
data want (drop=old_date);
set have (rename=(date=old_date));
retain date;
if old_date ne "" then date=old_date;
run;
Do you have an id number of some kind? If so I'd make sure it was sorted like this, that'll make sure all of your dates are in the right place.:
data have;
infile cards dsd;
informat date date9.;
format date date9.;
input ID$ date;
cards;
1,12NOV2013
1,
1,
2,13NOV2013
2,
2,
3,14NOV2013
3,
3,
;
proc sort data=have;by id descending date;
data want;
set have;
by id descending date;
retain _date;
if first.id then _date = date;
if missing(date) then date = _date;
drop _:;
run;
I gave just very small part of my dataset, in overall i have hundreds of date observations (not just three) and i have more variables.
I doesn't matter how many observations there are, the logic is the same:
- Step 1 - Sort the data into the order you require it.
- Step 2 - Loop over the dataset (datastep), and for each row identify if variable is missing and if so replace the missing with the retained value.
Here's an approach that is quick and easy. However, note that it will affect missing values for ALL your variables, not just ones that you have in mind such as DATE:
data middle;
set have;
dummy='abc';
run;
data want (drop=dummy);
update middle (obs=0) middle;
by dummy;
output;
run;
Astounding wrote:
Here's an approach that is quick and easy. However, note that it will affect missing values for ALL your variables, not just ones that you have in mind such as DATE:
When you have variable that you want want to LOCF then bring them in using a SET statement.
check the below code ,this will work:
data have;
input DATE;
format date date9.;
informat date date9.;
cards;
12Nov2013
13Nov2013
14Nov2013
;
run;
data want(drop=i);
set have;
do i=0 to 2 ;
output;
end;
run;
data filled (drop=_date);
set empty (rename=(date=_date));
retain date;
if _date NE . then date = _date;
run;
Hth, Eric
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.