Retaining last date-time ignores single valid entry

[lyton80]

Hi,

I am using a macro like this below to select the last non null date in a de-duping process.

%macro retbl(nvar=age,i=1);

data kplastn&i;

set datasrt(keep=&nvar hosp_visit_id event_Time);

length tempn 8.;

by hosp_visit_id evnt_Time;

retain tempn;

if  first.hosp_visit_id then tempn=.;

     if missing(&nvar) then

     &nvar = input(tempn, 8.);

  else tempn=input(&nvar,??8.);

  if last.hosp_visit_id ;

run;

%mend;

%retbl(nvar=Date_Time,i=6)

This works fine unless the date is missing for all the data except only one valid entry.

illustration:

input data:

obs     Date_time

1              .

2              .

3            05APR15:10:40:20

4              .

5              .

output data set

obs    Date_time

1         .

desired output data set

obs Data_time

1.    05APR15:10:40:20

Thanksa

[PGStats · Accepted solution]

It is not clear what you want since your keep list contains three variables and your example outputs only one. It is not clear either whether you want Date_Time to be character or numeric.

You could build upon the following simple datastep structure:

data kplastn6;

do until(last.Hosp_Visit_Id);

  set dataSrt; by Hosp_Visit_Id;

  if not missing(Date_Time) then tempDT = Date_Time;

  end;

Date_Time = tempDT;

keep Hosp_Visit_Id Event_Time Date_Time;

run;

PG

[lyton80]

@PGstats: Thanks..it works perfect. Just for academic purposes, can you explain how it works..I find it difficult to understand when SAS statements appear between data and set statements.

[PGStats]

This datastep programming method is called the DOW loop. I learned about it on this forum! The not so obvious part of it for me was that you can refer to last.Hosp_Visit_Id before the BY statement. Once you know that, the rest is well explained in many published papers. For example, in http://support.sas.com/resources/papers/proceedings09/038-2009.pdf.

PG 

[Patrick]

In case you wouldn't select any record for hosp_visit if ALL event_times are missing then you could get away with something as below:

data kplastn&i;

  set datasrt(keep=&nvar hosp_visit_id event_Time where=(not missing(event_Time)));

  by hosp_visit_id evnt_Time;

  if last.hosp_visit_id;

run;