topic Re: Keep only most recent records in SAS Programming

Keep only most recent records

seltonsy — Fri, 02 Mar 2018 03:46:44 GMT

Hi everyone,

I have a data with Diagnoses at each year of Medical exam. I am trying to select ALL Diagnosis data (all lines) from the MOST RECENT EXAM ONLY, for each ID.

Please, can you help me with the coding, see below the database structure.

Thanks in advance,

ID	Calendar Year	Diagnosis
1	2016	TB
1	2016	No health condition
1	2016	No health condition
2	2014	TB
2	2016	Inactive/Latent Tuberculosis
3	2014	TB
3	2016	Inactive/Latent Tuberculosis
3	2016	HIV
3	2016	Hypertension
4	2015	Cancer
4	2014	No health condition
4	2016	TB

Re: Keep only most recent records

kiranv_ — Fri, 02 Mar 2018 04:32:53 GMT

how the output should look like.

Re: Keep only most recent records

seltonsy — Fri, 02 Mar 2018 05:25:01 GMT

It doesn't matter much. If you have more than one way in your mind feel free to share. Well appreciated..
Thanks.

Re: Keep only most recent records

Patrick — Fri, 02 Mar 2018 09:01:47 GMT

What most forum members would appreciate is if you could post sample data in the form of a working SAS data step so we don't have to do the work for you.

It's not clear to me what constitutes the most recent exam. Below code sample assumes you're just after all the rows per ID with the latest year.

data have;
  infile datalines dlm=' ' truncover;
  input ID $ Calendar_Year Diagnosis $60.;
  datalines;
1 2016  TB 
1 2016 No health condition 
1 2016 No health condition 
2 2014 TB 
2 2016 Inactive/Latent Tuberculosis 
3 2014 TB 
3 2016 Inactive/Latent Tuberculosis 
3 2016 HIV 
3 2016 Hypertension 
4 2015 Cancer 
4 2014 No health condition 
4 2016 TB 
;
run;

proc sql;
  create table want as
    select *
    from have
    group by id
    having Calendar_Year=max(Calendar_Year)
  ;
quit;

Re: Keep only most recent records

Satish_Parida — Fri, 02 Mar 2018 09:07:52 GMT

/*The Code will look like this*/
proc sort data=have;
by ID Calendar_Year;
run;

data want;
set have;
by ID Calendar_Year;
if last.ID then output;
run;

/*But if you have a Diagnoses_Date then It would be Perfect*/
proc sort data=have;
by ID Diagnoses_Date;
run;

data want;
set have;
by ID Diagnoses_Date;
if last.ID then output;
run;

Re: Keep only most recent records

seltonsy — Fri, 02 Mar 2018 13:15:51 GMT

Thanks for the effort,

I wanted to have not only the last line but all lines that have the latest exam date

for example, you go to the doctor and he writes each diagnosis on a separate line with the same date for all exam
thanks

Re: Keep only most recent records

Kurt_Bremser — Fri, 02 Mar 2018 13:40:26 GMT

How 'bout

proc sql;
create table want as
select *
from have
group by id
having diag_date = max(diag_date);
quit;

Re: Keep only most recent records

Satish_Parida — Fri, 02 Mar 2018 14:13:08 GMT

In That Case:

Using Solution provide by @Kurt_Bremser

proc sql;
create table want as
select a.* from have a ,
(select id,diag_date
from have
group by id
having diag_date = max(diag_date))
where a.id=b.id and a.diag_date=b.diag_date
;
quit;

Re: Keep only most recent records

kiranv_ — Sat, 03 Mar 2018 06:25:03 GMT

answer is important but asking question is even more important. it makes you successful. if you want to be successful it is very very important to ask question correctly and in effective manner, where in no one have to guess. Most of work in field depends on how to ask proper questions in business or in any other field