Help using Base SAS procedures

latest date (dtc) on or prior to the rand_dtc

Reply
Contributor
Posts: 34

latest date (dtc) on or prior to the rand_dtc

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

Respected Advisor
Posts: 3,156

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

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

Contributor
Posts: 34

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

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

Respected Advisor
Posts: 3,156

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

Then the second solution should work for you.

Good Luck,

Haikuo

Contributor
Posts: 34

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

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

Respected Advisor
Posts: 3,156

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

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

Contributor
Posts: 34

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

Hi Haikuo,

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

Thanks

Respected Advisor
Posts: 3,156

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

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

Contributor
Posts: 34

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

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

Regular Contributor
Posts: 168

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

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

Contributor
Posts: 21

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

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;

Ask a Question
Discussion stats
  • 10 replies
  • 429 views
  • 6 likes
  • 4 in conversation