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 |
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;
how the output should look like.
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;
/*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;
How 'bout
proc sql;
create table want as
select *
from have
group by id
having diag_date = max(diag_date);
quit;
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;
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.