BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DathanMD
Obsidian | Level 7

Dear Friend,

I am new to SAS and can’t find way around some code; please help if you can.

I am working on data set (see first rows below of the dataset below) and would like to pick patients with two GFR=1 and date between them (GFR_D) between them of at least 90 days (≥90 days). In clinical settings this is how patients diagnosed with chronic kidney disease (CKD). Patients must have at least two subsequent GFR measures that are more than 90 days apart before they are labeled CKD patients.  The abnormal measures (GFR=1) must not have a normal value between them ( e.g. patient 8 doesn’t have CKD!)

Data I have.

PATIENT

GFR_D

GFR

1

2011-01-31

0

1

2011-06-16

1

1

2011-10-26

1

1

2012-03-13

0

1

2012-07-19

0

1

2012-11-22

1

1

2013-02-27

1

1

2013-06-28

1

1

2013-11-08

0

1

2014-03-11

0

1

2014-07-02

1

2

2011-04-20

0

2

2011-09-22

0

2

2012-01-16

0

2

2012-04-19

0

3

2012-07-26

1

3

2012-11-15

0

3

2013-03-21

1

4

2013-07-09

1

4

2013-10-29

1

4

2015-06-17

1

5

2011-02-01

0

5

2011-05-02

0

6

2011-07-31

1

6

2011-10-29

0

7

2011-06-16

1

7

2011-10-26

0

7

2012-03-13

1

7

2012-07-19

0

7

2012-11-22

1

7

2013-02-27

1

7

2013-06-28

1

8

2011-06-16

1

8

2011-10-26

0

8

2012-03-13

1

8

2012-07-19

0

8

2012-11-22

1

8

2013-11-27

0

9

2011-06-16

1

9

2011-10-26

0

9

2012-03-13

1

9

2012-07-19

0

9

2012-11-22

1

9

2013-11-27

1

9

2013-12-28

1

10

2013-11-27

1

10

2016-05-28

1

 

The data I want should contain only patients who have GFR=1 at any two dates that are at least 90 days apart. In would also like the date when the patient became diagnosed with CKD (That is the first date when GFR was 1 and the subsequent GFR was more than 90 days after and = 1)

Data I want

PATIENT

GFR_D

1

2011-06-16

4

2013-07-09

7

2012-11-22

10

2013-11-27

 

Thank you so much.

Dathan Byonanebye

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

That will be easier in a data step. Use RETAIN to remember if/when the earlier test was and whether you found any with difference larger than 90 days.

data want ;
  set have ;
  by patient gfr_d ;
  retain first_date diff found;
  format first_date yymmdd10.;
  if first.patient then call missing(first_date,diff,found);
  if (not found) then do;
    if gfr then do;
      if missing(first_date) then first_date=gfr_d ;
      else do;
        diff = gfr_d - first_date;
        if diff >= 90 then found=1;
      end;
    end;
    else call missing(first_date);
  end;
  if last.patient and found then output want;
  keep patient first_date diff ;
run;
Obs    PATIENT    first_date    diff

 1         1      2011-06-16     132
 2         4      2013-07-09     112
 3         7      2012-11-22      97
 4         9      2012-11-22     370
 5        10      2013-11-27     913

View solution in original post

6 REPLIES 6
Tom
Super User Tom
Super User

Why isn't patient 3 on the list?  It has dates of  2012-07-26  and 2013-03-21 .

data have;
  input PATIENT GFR_D  GFR ;
  informat gfr_d yymmdd.;
  format gfr_d yymmdd10.;
  put patient gfr_d gfr ;
cards;
1 2011-01-31 0
1 2011-06-16 1
1 2011-10-26 1
1 2012-03-13 0
1 2012-07-19 0
1 2012-11-22 1
1 2013-02-27 1
1 2013-06-28 1
1 2013-11-08 0
1 2014-03-11 0
1 2014-07-02 1
2 2011-04-20 0
2 2011-09-22 0
2 2012-01-16 0
2 2012-04-19 0
3 2012-07-26 1
3 2012-11-15 0
3 2013-03-21 1
4 2013-07-09 1
4 2013-10-29 1
4 2015-06-17 1
5 2011-02-01 0
5 2011-05-02 0
6 2011-07-31 1
6 2011-10-29 0
7 2011-06-16 1
7 2011-10-26 0
7 2012-03-13 1
7 2012-07-19 0
7 2012-11-22 1
7 2013-02-27 1
7 2013-06-28 1
8 2011-06-16 1
8 2011-10-26 0
8 2012-03-13 1
8 2012-07-19 0
8 2012-11-22 1
8 2013-11-27 0
9 2011-06-16 1
9 2011-10-26 0
9 2012-03-13 1
9 2012-07-19 0
9 2012-11-22 1
9 2013-11-27 1
9 2013-12-28 1
10 2013-11-27 1
10 2016-05-28 1
;
proc sql;
  select patient
       , min(gfr_d) as first_date format=yymmdd10.
       , max(gfr_d) as last_date format=yymmdd10.
       , range(gfr_d) as range
  from have
  where gfr=1
  group by patient
  having range(gfr_d) >= 90
  ;
quit;
PATIENT  first_date   last_date     range
------------------------------------------
       1  2011-06-16  2014-07-02      1112
       3  2012-07-26  2013-03-21       238
       4  2013-07-09  2015-06-17       708
       7  2011-06-16  2013-06-28       743
       8  2011-06-16  2012-11-22       525
       9  2011-06-16  2013-12-28       926
      10  2013-11-27  2016-05-28       913
DathanMD
Obsidian | Level 7
Hi Tom, thanks for your help. However, a patient is not supposed to have a
GFR=0 between teh two abnormal GFRs(GFR=1). Patient must have two
subsequent (one following the other) tests that are more than 90 days apart
to have CKD.

Thanks

Dathan
Reeza
Super User

Shouldn't patient 9 be included as well?

 

9 2011-06-16 1
9 2011-10-26 0
9 2012-03-13 1
9 2012-07-19 0
9 2012-11-22 1
9 2013-11-27 1 ->365+ days apart?
9 2013-12-28 1

 

If so, this works for me. You'll need to unduplicate the list but this lets you track the logic to ensure it's correct. 

 

data want;
set have;
by patient gfr notsorted;
x=dif(gfr_d);
if first.gfr then x=.;

if gfr=1 and not first.gfr  and x>90 then flag=1;
else flag=0;
run;

Unique List

data temp;
set have;
by patient gfr notsorted;
x=dif(gfr_d);
if first.gfr then x=.;

if gfr=1 and not first.gfr  and x>90 then output;
run;

proc sql;
create table want as
select distinct patient
from temp;
quit;
Tom
Super User Tom
Super User

That will be easier in a data step. Use RETAIN to remember if/when the earlier test was and whether you found any with difference larger than 90 days.

data want ;
  set have ;
  by patient gfr_d ;
  retain first_date diff found;
  format first_date yymmdd10.;
  if first.patient then call missing(first_date,diff,found);
  if (not found) then do;
    if gfr then do;
      if missing(first_date) then first_date=gfr_d ;
      else do;
        diff = gfr_d - first_date;
        if diff >= 90 then found=1;
      end;
    end;
    else call missing(first_date);
  end;
  if last.patient and found then output want;
  keep patient first_date diff ;
run;
Obs    PATIENT    first_date    diff

 1         1      2011-06-16     132
 2         4      2013-07-09     112
 3         7      2012-11-22      97
 4         9      2012-11-22     370
 5        10      2013-11-27     913
DathanMD
Obsidian | Level 7

Thank you Tom

SAS Innovate 2025: Call for Content

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!

Submit your idea!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 6 replies
  • 1108 views
  • 3 likes
  • 3 in conversation