topic Minimum of prior values in SAS Programming

Minimum of prior values

Manikanta — Wed, 29 Nov 2017 04:50:13 GMT

data one;
input pt $ visit $ sum date ;
datalines;
a1 scr 100   01jan2015
a1 w1 150   02jan2015
a1 w2 125   03jan2015
a1 w3 80   04jan2015
a1 w4 90   05jan2015
a1 w5 70   06jan2015
a2 scr 100 01jan2015
a2 w1 125 02jan2015
a2 w2 150 03jan2015
;
run;

Can any one please help me to get below output :

pt visit sum date minimum visit_min
a1 scr 100 01jan2015 .
a1 w1 150 02jan2015 100 SCR
a1 w2 125 03jan2015 100 SCR
a1 w3 80 04jan2015 100 SCR
a1 w4 90 05jan2015 80 w3
a1 w5 70 06jan2015   100 SCR
a2 scr 100 01jan2015   .
a2 w1 125 02jan2015   100 SCR
a2 w2 150 03jan2015   100   SCR

minimum would be the minimum recorded (SUM) value from all visits prior to the current visit

If the current visit (SUM) is lowest so far in prior visits, then use (SUM) value of SCR

Re: Minimum of prior values

HB — Tue, 28 Nov 2017 20:40:47 GMT

1. Should the last two records in your desired output should be

a2 w1 125 100 SCR
a2 w2 150 100 SCR

2. Can we always count of the first instance of a patient ID to be a visit type of SCR? If not, can the data be sorted in that fashion?

3. Are all visits for a patient ID to be considered at once or is it possible patient A1 would have two visit groups (perhaps separated by a time variable we don't have) that would need to be considered separately?

Re: Minimum of prior values

Manikanta — Wed, 29 Nov 2017 04:55:19 GMT

1. Should the last two records in your desired output should be

a2 w1 125 100 SCR
a2 w2 150 100 SCR

ANS: Yes , Output looks like below :

pt visit sum date minimum visit_min
a1 scr 100 01jan2015 .
a1 w1 150 02jan2015   100 SCR
a1 w2 125 03jan2015 100 SCR
a1 w3 80 04jan2015 100 SCR
a1 w4 90 05jan2015 80 w3
a1 w5 70   06jan2015   100 SCR
a2 scr 100  01jan2015   .
a2 w1 125   02jan2015   100 SCR
a2 w2 150   03jan2015 100   SCR

2. Can we always count of the first instance of a patient ID to be a visit type of SCR? If not, can the data be sorted in that fashion?

Ans: Patient ID is sorted based on the date ,So SCR can occur at any instance .

Yes ,All visit for a patient ID to be consider at once .

Re: Minimum of prior values

Ksharp — Wed, 29 Nov 2017 14:27:21 GMT

If I understood your question.

data one;
input pt $ visit $ sum date $ ;
datalines;
a1 scr 100    01jan2015         
a1 w1 150    02jan2015
a1 w2 125    03jan2015
a1 w3 80      04jan2015         
a1 w4 90      05jan2015         
a1 w5 70      06jan2015         
a2 scr 100   01jan2015         
a2 w1 125   02jan2015         
a2 w2 150   03jan2015         
;
run;
data want;
 set one;
 by pt;
 length visit_min $ 80 ;
 retain minimum  visit_min;
 if first.pt then do;visit_min=' ';minimum=.;end;
 output;
 if first.pt then do;minimum=sum;visit_min=visit;end;
  else if sum<minimum  then do;minimum=sum;visit_min=visit;end;
 run;

Re: Minimum of prior values

andreas_lds — Wed, 29 Nov 2017 15:48:32 GMT

@Manikanta wrote:

2. Can we always count of the first instance of a patient ID to be a visit type of SCR? If not, can the data be sorted in that fashion?

Ans: Patient ID is sorted based on the date ,So SCR can occur at any instance .

Please add that case to your example data to explain what you expect as result.

In the meantime i created something really ugly. I am sure that the code will be easier to maintain by using two dow-loops - the first to find the scr-observation for a patient and the second to fill minimum and visit_min.

data want;
   set one;
   by pt;

   length 
      minimum 8 visit_min $ 3
      bakMinimum scrSum 8 bakVisit $ 3
      rc 8
   ;
   retain bak: scrSum;

   drop bak: scrSum rc;

   if _n_ = 1 then do;
      declare hash h(dataset: 'work.one(keep= pt sum visit rename=(sum=minimum) where=(visit = "scr"))');
      h.defineKey('pt');
      h.defineData('minimum');
      h.defineDone();
   end;

   if first.pt then do;
      bakMinimum = sum;

      if visit = "scr" then do;
         scrSum = sum;
         bakVisit = "scr";
         call missing(minimum, visit_min);
      end;
      else do;
         rc = h.find();
         visit_min = "scr";
         scrSum = Minimum;
         bakMinimum = min(sum, minimum);
         bakVisit = ifc(sum < minimum, visit, "scr");
      end;
   end;
   else do;
      if sum >= bakMinimum then do;
         minimum = bakMinimum;
         visit_min = bakVisit;
      end;
      else do;
         minimum = scrSum;
         visit_min = "scr";
         bakMinimum = sum;
         bakVisit = visit;
      end;

      if visit = "scr" then do;
         call missing(minimum, visit_min);
      end;
   end;
run;