topic Re: Retaining last date-time ignores single valid entry in SAS Procedures

Retaining last date-time ignores single valid entry

lyton80 — Tue, 07 Apr 2015 19:04:33 GMT

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

Re: Retaining last date-time ignores single valid entry

PGStats — Tue, 07 Apr 2015 20:54:15 GMT

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;

Re: Retaining last date-time ignores single valid entry

Patrick — Tue, 07 Apr 2015 22:54:19 GMT

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;

Re: Retaining last date-time ignores single valid entry

lyton80 — Wed, 08 Apr 2015 16:27:33 GMT

@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.

Re: Retaining last date-time ignores single valid entry

PGStats — Wed, 08 Apr 2015 21:37:50 GMT

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.