BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lyton80
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

4 REPLIES 4
PGStats
Opal | Level 21

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
lyton80
Obsidian | Level 7

@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
Opal | Level 21

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
Patrick
Opal | Level 21

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;

SAS Innovate 2025: Call for Content

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!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 1019 views
  • 3 likes
  • 3 in conversation