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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.