topic Re: latest date (dtc) on or prior to the rand_dtc in SAS Procedures

latest date (dtc) on or prior to the rand_dtc

Almighty — Wed, 05 Feb 2014 18:55:11 GMT

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

Re: latest date (dtc) on or prior to the rand_dtc

Haikuo — Wed, 05 Feb 2014 19:36:08 GMT

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

Re: latest date (dtc) on or prior to the rand_dtc

Almighty — Wed, 05 Feb 2014 19:47:36 GMT

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

Re: latest date (dtc) on or prior to the rand_dtc

Haikuo — Wed, 05 Feb 2014 19:59:30 GMT

Then the second solution should work for you.

Good Luck,

Haikuo

Re: latest date (dtc) on or prior to the rand_dtc

Almighty — Thu, 06 Feb 2014 15:42:51 GMT

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.

proc sql;

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

Re: latest date (dtc) on or prior to the rand_dtc

Haikuo — Thu, 06 Feb 2014 16:38:49 GMT

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

Re: latest date (dtc) on or prior to the rand_dtc

sam369 — Thu, 06 Feb 2014 16:55:36 GMT

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

Re: latest date (dtc) on or prior to the rand_dtc

Almighty — Thu, 06 Feb 2014 16:57:53 GMT

Hi Haikuo,

it didn't work in addtion i was getting duplicate records also...

Thanks

Re: latest date (dtc) on or prior to the rand_dtc

Haikuo — Thu, 06 Feb 2014 17:42:16 GMT

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

Re: latest date (dtc) on or prior to the rand_dtc

Marina — Thu, 06 Feb 2014 17:46:46 GMT

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;

Re: latest date (dtc) on or prior to the rand_dtc

Almighty — Thu, 13 Feb 2014 15:47:25 GMT

Hi Haikuo,

Thanks for the help. I just posted another questions, would you kindly find sometime to help. I tried to do by myself, but somehow I am missing the logic:

Thanks