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

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!!!

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

6 REPLIES 6
ballardw
Super User

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.

art297
Opal | Level 21

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

 

 

sas_student1
Quartz | Level 8

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;
art297
Opal | Level 21

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

 

 

sas_student1
Quartz | Level 8

@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!!!

art297
Opal | Level 21

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

 

 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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