BookmarkSubscribeRSS Feed
Almighty
Fluorite | Level 6

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

10 REPLIES 10
Haikuo
Onyx | Level 15

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

Almighty
Fluorite | Level 6

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

Haikuo
Onyx | Level 15

Then the second solution should work for you.

Good Luck,

Haikuo

Almighty
Fluorite | Level 6

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

Haikuo
Onyx | Level 15

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

Almighty
Fluorite | Level 6

Hi Haikuo,

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

Thanks

Haikuo
Onyx | Level 15

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

Almighty
Fluorite | Level 6

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

sam369
Obsidian | Level 7

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

Marina
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1354 views
  • 6 likes
  • 4 in conversation