Hi,
I am trying to get records with the latest assessment date on or prior to the rand_dtc. It would be great guru's can provide the logic or code. Thanks for your time and help.
Thanks
EX: The data I have
Pt_ID wt ht dtc rand_dtc
101 70 180 4/10/2010 5/20/2010
101 68 180 4/15/2010 5/20/2010
101 65 180 4/20/2010 5/20/2010
101 71 180 5/18/2010 5/20/2010
102 65 160 6/10/2010 8/15/2010
102 66 160 8/15/2010 8/15/2010
103 72 175 3/10/2010 6/10/2010
103 71 175 4/10/2010 6/10/2010
Data need:
Pt_ID wt ht dtc rand_dtc
101 71 180 5/18/2010 5/20/2010
102 66 160 8/15/2010 8/15/2010
103 71 175 4/10/2010 6/10/2010
data have;
input (Pt_ID wt ht) (:$) (dtc rand_dtc) (:mmddyy10.);
format dtc rand_dtc mmddyy10.;
cards;
101 70 180 4/10/2010 5/20/2010
101 68 180 4/15/2010 5/20/2010
101 65 180 4/20/2010 5/20/2010
101 71 180 5/18/2010 5/20/2010
102 65 160 6/10/2010 8/15/2010
102 66 160 8/15/2010 8/15/2010
103 72 175 3/10/2010 6/10/2010
103 71 175 4/10/2010 6/10/2010
;
/*if dtc always less equal to rand_dtc*/
proc sql;
create table want_1 as
select * from have
group by Pt_ID
having dtc = max(dtc);
quit;
/*else slower but more robust solution*/
proc sql;
create table want_1 as
select * from have (where=(rand_dtc-dtc >= 0))
group by Pt_ID
having dtc = max(dtc);
quit;
Haikuo
Hi Hai.Kuo:
As usual you are the best. I am expecting for several patients DTC is more than RAND_DTC: example below pt_ID: 104.
EX: The data I have
Pt_ID wt ht dtc rand_dtc
101 70 180 4/10/2010 5/20/2010
101 68 180 4/15/2010 5/20/2010
101 65 180 4/20/2010 5/20/2010
101 71 180 5/18/2010 5/20/2010
102 65 160 6/10/2010 8/15/2010
102 66 160 8/15/2010 8/15/2010
103 72 175 3/10/2010 6/10/2010
103 71 175 4/10/2010 6/10/2010
104 80 180 5/10/2010 5/25/2010
104 79 180 5/26/2010 5/25/2010
104 78 180 5/20/2010 5/25/2010
Data need:
Pt_ID wt ht dtc rand_dtc
101 71 180 5/18/2010 5/20/2010
102 66 160 8/15/2010 8/15/2010
103 71 175 4/10/2010 6/10/2010
104 78 180 5/20/2010 5/25/2010
Then the second solution should work for you.
Good Luck,
Haikuo
This worked, but:
if the DTC values are null then those records are dropping. But I wanted to include those records with DTC values are null.
create table asa1 as
select * from asa (where=(randdt-cmstdt >= 0))
group by subjid
having cmstdt = max(cmstdt);
quit;
I used this code, where some of the records have CMSTDT values are null. I don't want those records to be dropped. Could you please help me.
Appreciate your help.
EX: The data I have
Pt_ID wt ht dtc rand_dtc
101 71 180 . 5/20/2010
102 65 160 6/10/2010 8/15/2010
102 66 160 8/15/2010 8/15/2010
103 72 175 3/10/2010 6/10/2010
103 71 175 4/10/2010 6/10/2010
104 80 180 5/10/2010 5/25/2010
104 79 180 5/26/2010 5/25/2010
104 78 180 5/20/2010 5/25/2010
Data need:
Pt_ID wt ht dtc rand_dtc
101 71 180 . 5/20/2010
102 66 160 8/15/2010 8/15/2010
103 71 175 4/10/2010 6/10/2010
104 78 180 5/20/2010 5/25/2010
For that, we have an easy fix:
proc sql;
create table want_1 as
select * from have (where=(sum(rand_dtc,-dtc) >= 0))
group by Pt_ID
having dtc = max(dtc);
quit;
Haikuo
Hi Haikuo,
it didn't work in addtion i was getting duplicate records also...
Thanks
Please post some sample data. If you have duplicated dtc within the sample ft_id, yes, you will get duplicates in term of dtc. In that case, you want to add another step such as proc sort, or data step to remove them. The following only takes care of those where the whole record is identical.
proc sql;
create table want_1 as
select distinct * from have (where=(sum(rand_dtc,-dtc) >= 0))
group by Pt_ID
having dtc = max(dtc);
quit;
Good luck,
Haikuo
Hi Almighty,
Haikuo sol is better than mine , but another approach
proc sql;
create table want_1 as
select *,not dtc as _dtc from have
where (rand_dtc-dtc) ge 0 or calculated _dtc eq 1
group by Pt_ID
having dtc = max(dtc);
quit;
HTH
Sam
If dtc and rand_dtc in your data set have data type DATE, this should work:
proc sql;
create table need as
select *
from have
where rand_dtc >= dtc
group by Pt_ID
having dtc = max(dtc);
quit;
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!
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.