Help using Base SAS procedures

How to calcuate date diff with flagging rows that are within a certain date

Accepted Solution Solved
Reply
Contributor
Posts: 43
Accepted Solution

How to calcuate date diff with flagging rows that are within a certain date

Hello,

 

I have a dataset that has patient ID, Date of service and a list of procedures that patient received (proc1 to proc5, in reality the procedure list goes to 22 but I thought for this 5 would suffice, hopefully I am right).

 

What I need to do is determine how many of the patients who received a specific procedure, H0014 in this case, also received the other procedures within 14 days.

 

So in the example table below I would want patient 2 , patient 3 (with the date of 2/11/17) and patient 4 (both the 3/4/17 and 4/13/17 visits) to be flagged.

 

Data Have:

 

Pat_IDDate_of_serviceProc1Proc2Proc3Proc4Proc5
19/25/201799213    
110/4/201799213    
110/11/2017H0014    
210/20/2017H0014    
211/3/2017 99214   
312/28/201799214    
32/4/201799214    
32/11/2017H001499213   
33/11/2017  99215  
33/15/201799213    
43/4/2017H0014    
43/10/2017   99214 
44/10/2017 99213   
44/13/2017  H0014  
44/14/201799216    

 

Data Want:

here I have added a flag column that using 1 or 2 that indicates which of the H0014 row is coupled with the row that has the other procs within 14 days (this would be with a 2). Of course if there are two (or more procs) in one row with H0014 then the flag with 1 will suffice.

Pat_IDDate_of_serviceProc1Proc2Proc3Proc4Proc5Flag
19/25/201799213    0
110/4/201799213    0
110/11/2017H0014    0
210/20/2017H0014    1
211/3/2017 99214   2
312/28/201799214    0
32/4/201799214    0
32/11/2017H001499213   1
33/11/2017  99215  0
33/15/201799213    0
43/4/2017H0014    1
43/10/2017   99214 2
44/10/2017 99213   0
44/13/2017  H0014  1
44/14/201799216    2

 

is this too complicated to do?

 

Thank you in advance!!!


Accepted Solutions
Solution
‎03-21-2018 10:26 PM
PROC Star
Posts: 8,167

Re: How to calcuate date diff with flagging rows that are within a certain date

Posted in reply to sas_student1

You have to take more than one pass thru the data. I think the following does what you want:

data have;
  informat Pat_ID $2. Date_of_service mmddyy10. Proc1 $5. Proc2 $5. Proc3 $5. Proc4 $5. Proc5 $5.;
  input Pat_ID Date_of_service Proc1 Proc2 Proc3 Proc4 Proc5 ;
  format Date_of_service date9.;
  datalines;
A1 092517 99213 . . . .
A1 100417 99213 . . . .
A1 101117 H0014 . . . .
A2 102017 H0014 . . . .
A2 110317 . 99214 . . .
A3 122817 99214 . . . .
A3 020417 99214 . . . .
A3 021117 H0014 99213 . . .
A3 031117 . . 99215 . .
A3 031517 99213 . . . .
A4 030417 H0014 . . . .
A4 031017 . . . 99214 .
A4 041017 . 99213 . . .
A4 041317 . . H0014 . .
A4 041417 99216 . . . .
;
run;

data need (drop=first_date procs: i j);
  set have;
  by Pat_ID;
  array inprocs(5) proc1-proc5;
  array procs(5);
  retain first_date;
  flag=0;
  if first.Pat_ID then call missing(first_date);
  if 'H0014' in inprocs then do;
    flag=1;
    first_date=Date_of_service;
  end;
  else if not missing(first_date) and Date_of_service-first_date le 14 then do;
    j=0;
    do i=1 to 5;
      if inprocs(i) ne 'H0014' then do;
        if not missing(inprocs(i)) then do;
          procs(i)=inprocs(i);
          j+1;
        end;
      end;
    end;
    if j gt 0 then do;
      flag=2;
      call missing(first_date);
    end;
  end;
  else flag=0;
  obs=_n_;
run;

proc sort data=need;
  by descending obs;
run;

data need (drop=foundone);
  set need;
  by descending Pat_ID;
  array inprocs(5) proc1-proc5;
  retain foundone;
  if first.Pat_ID then foundone=0;
  if not foundone and 'H0014' in inprocs then do;
    flag=0;
    foundone=1;
  end;
  else if flag eq 2 then foundone=1;
run;

proc sort data=need out=want (drop=obs);
  by obs;
run;

Art, CEO, AnalystFinder.com

 

 

View solution in original post


All Replies
Super User
Posts: 13,583

Re: How to calcuate date diff with flagging rows that are within a certain date

Posted in reply to sas_student1

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

 

I say that because this process gets much easier if the data is moved to a single record of patient, date, procedure but I'm not going to take the time to attempt to create a data set from your example.

PROC Star
Posts: 8,167

Re: How to calcuate date diff with flagging rows that are within a certain date

[ Edited ]
Posted in reply to sas_student1

I think that something like the following would do what you want:

data want (drop=first_date procs: i j);
  set have;
  by Pat_ID;
  array inprocs(5) proc1-proc5;
  array procs(5);
  retain first_date;
  flag=0;
  if first.Pat_ID then call missing(first_date);
  if 'H0014' in inprocs then do;
    flag=1;
    first_date=Date_of_service;
  end;
  else if not missing(first_date) and Date_of_service-first_date le 14 then do;
    j=0;
    do i=1 to 5;
      if inprocs(i) ne 'H0014' then do;
        if not missing(inprocs(i)) then do;
          procs(i)=inprocs(i);
          j+1;
        end;
      end;
    end;
    if j gt 0 then do;
      flag=2;
      call missing(Date_of_service);
    end;
  end;
  else flag=0;
run;

Art, CEO, AnalystFinder.com

 

 

Contributor
Posts: 43

Re: How to calcuate date diff with flagging rows that are within a certain date

Thanks @art297 and @ballardw.

@art297 your code almost worked. Sorry I made the mistake of giving you pat_id as numeric when in reality it was character so the first (pat_id) with a character variable didn't work. But if I replace that first(pat_id) to first.pat_id it sets some of the dates as missing, not sure why.

 

Suggestions on how to fix?

 

Thank you so much!

 

Here is the code:

data have;
informat Pat_ID $2. Date_of_service mmddyy10. Proc1 $5. Proc2 $5. Proc3 $5. Proc4 $5. Proc5 $5.;
input Pat_ID Date_of_service Proc1 Proc2 Proc3 Proc4 Proc5 ;
format Date_of_service date9.;
datalines;
A1 092517 99213 . . . .
A1 100417 99213 . . . .
A1 101117 H0014 . . . .
A2 102017 H0014 . . . .
A2 110317 . 99214 . . .
A3 122817 99214 . . . .
A3 020417 99214 . . . .
A3 021117 H0014 99213 . . .
A3 031117 . . 99215 . .
A3 031517 99213 . . . .
A4 030417 H0014 . . . .
A4 031017 . . . 99214 .
A4 041017 . 99213 . . .
A4 041317 . . H0014 . .
A4 041417 99216 . . . .
;
run;
PROC Star
Posts: 8,167

Re: How to calcuate date diff with flagging rows that are within a certain date

Posted in reply to sas_student1

It didn't work because I had another error in the code. Try:

data have;
informat Pat_ID $2. Date_of_service mmddyy10. Proc1 $5. Proc2 $5. Proc3 $5. Proc4 $5. Proc5 $5.;
input Pat_ID Date_of_service Proc1 Proc2 Proc3 Proc4 Proc5 ;
format Date_of_service date9.;
datalines;
A1 092517 99213 . . . .
A1 100417 99213 . . . .
A1 101117 H0014 . . . .
A2 102017 H0014 . . . .
A2 110317 . 99214 . . .
A3 122817 99214 . . . .
A3 020417 99214 . . . .
A3 021117 H0014 99213 . . .
A3 031117 . . 99215 . .
A3 031517 99213 . . . .
A4 030417 H0014 . . . .
A4 031017 . . . 99214 .
A4 041017 . 99213 . . .
A4 041317 . . H0014 . .
A4 041417 99216 . . . .
;
run;
data want (drop=first_date procs: i j);
  set have;
  by Pat_ID;
  array inprocs(5) proc1-proc5;
  array procs(5);
  retain first_date;
  flag=0;
  if first.Pat_ID then call missing(first_date);
  if 'H0014' in inprocs then do;
    flag=1;
    first_date=Date_of_service;
  end;
  else if not missing(first_date) and Date_of_service-first_date le 14 then do;
    j=0;
    do i=1 to 5;
      if inprocs(i) ne 'H0014' then do;
        if not missing(inprocs(i)) then do;
          procs(i)=inprocs(i);
          j+1;
        end;
      end;
    end;
    if j gt 0 then do;
      flag=2;
      call missing(first_date);
    end;
  end;
  else flag=0;
run;

Art, CEO, AnalystFinder.com

 

 

Contributor
Posts: 43

Re: How to calcuate date diff with flagging rows that are within a certain date

@art297 you rock!!!!

 

I think it did it. However, why does A1 get flagged? Is this because the calculation of within 14 days is regardless of when H0014 procedure is reported?  So if we wanted to make the rule where H0014 has to happen on the date or within 14 days before the other procedures are reported do we change the by to add mbr_id and procs or do we add it in the "first" command?

 

Thank you!!!!!

You are fabulous!!!

Solution
‎03-21-2018 10:26 PM
PROC Star
Posts: 8,167

Re: How to calcuate date diff with flagging rows that are within a certain date

Posted in reply to sas_student1

You have to take more than one pass thru the data. I think the following does what you want:

data have;
  informat Pat_ID $2. Date_of_service mmddyy10. Proc1 $5. Proc2 $5. Proc3 $5. Proc4 $5. Proc5 $5.;
  input Pat_ID Date_of_service Proc1 Proc2 Proc3 Proc4 Proc5 ;
  format Date_of_service date9.;
  datalines;
A1 092517 99213 . . . .
A1 100417 99213 . . . .
A1 101117 H0014 . . . .
A2 102017 H0014 . . . .
A2 110317 . 99214 . . .
A3 122817 99214 . . . .
A3 020417 99214 . . . .
A3 021117 H0014 99213 . . .
A3 031117 . . 99215 . .
A3 031517 99213 . . . .
A4 030417 H0014 . . . .
A4 031017 . . . 99214 .
A4 041017 . 99213 . . .
A4 041317 . . H0014 . .
A4 041417 99216 . . . .
;
run;

data need (drop=first_date procs: i j);
  set have;
  by Pat_ID;
  array inprocs(5) proc1-proc5;
  array procs(5);
  retain first_date;
  flag=0;
  if first.Pat_ID then call missing(first_date);
  if 'H0014' in inprocs then do;
    flag=1;
    first_date=Date_of_service;
  end;
  else if not missing(first_date) and Date_of_service-first_date le 14 then do;
    j=0;
    do i=1 to 5;
      if inprocs(i) ne 'H0014' then do;
        if not missing(inprocs(i)) then do;
          procs(i)=inprocs(i);
          j+1;
        end;
      end;
    end;
    if j gt 0 then do;
      flag=2;
      call missing(first_date);
    end;
  end;
  else flag=0;
  obs=_n_;
run;

proc sort data=need;
  by descending obs;
run;

data need (drop=foundone);
  set need;
  by descending Pat_ID;
  array inprocs(5) proc1-proc5;
  retain foundone;
  if first.Pat_ID then foundone=0;
  if not foundone and 'H0014' in inprocs then do;
    flag=0;
    foundone=1;
  end;
  else if flag eq 2 then foundone=1;
run;

proc sort data=need out=want (drop=obs);
  by obs;
run;

Art, CEO, AnalystFinder.com

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 204 views
  • 0 likes
  • 3 in conversation