Help using Base SAS procedures

Retaining last date-time ignores single valid entry

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Retaining last date-time ignores single valid entry

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


Accepted Solutions
Solution
‎04-07-2015 04:54 PM
Respected Advisor
Posts: 4,640

Re: Retaining last date-time ignores single valid entry

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

PG

View solution in original post


All Replies
Solution
‎04-07-2015 04:54 PM
Respected Advisor
Posts: 4,640

Re: Retaining last date-time ignores single valid entry

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

PG
Occasional Contributor
Posts: 15

Re: Retaining last date-time ignores single valid entry

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

Respected Advisor
Posts: 4,640

Re: Retaining last date-time ignores single valid entry

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 

PG
Respected Advisor
Posts: 3,886

Re: Retaining last date-time ignores single valid entry

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 222 views
  • 3 likes
  • 3 in conversation