BookmarkSubscribeRSS Feed
viollete
Calcite | Level 5

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



8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Steelers_In_DC
Barite | Level 11

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;

viollete
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Astounding
PROC Star

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;

data_null__
Jade | Level 19

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.

data have;
   input DATE1 $16.;
   date2 = date1;
  
if not missing(date1) then set sashelp.class point=_n_;
   output;
  
call missing(of _all_);
   cards;
12 Nov 2013


13 Nov 2013


14 Nov 2013
;;;;
   run;
proc print;
  
run;
data middle / view=middle;
   set have;
   dummy='abc';
  
run;
data want;
   update middle(obs=0 keep=dummy) middle(keep=dummy date:);
   by dummy;
   set have(drop=date:);
   output;
  
drop dummy;
   run;
proc print;
  
run;

Capture.PNG

Capture.PNG
Dalveer
Calcite | Level 5

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;

EricHoogenboom
Fluorite | Level 6

data filled (drop=_date);

   set empty (rename=(date=_date));

   retain date;

   if _date NE . then date = _date;

run;

Hth, Eric

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 960 views
  • 0 likes
  • 7 in conversation