DATA Step, Macro, Functions and more

Minimum of prior values

Reply
Occasional Contributor
Posts: 5

Minimum of prior values

[ Edited ]

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 

Super Contributor
Super Contributor
Posts: 260

Re: Minimum of prior values

Posted in reply to Manikanta

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?

Occasional Contributor
Posts: 5

Re: Minimum of prior values

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 . 

 

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?

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

Super Contributor
Posts: 498

Re: Minimum of prior values

Posted in reply to Manikanta

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;

 

 

Super User
Posts: 10,612

Re: Minimum of prior values

Posted in reply to Manikanta

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;
Ask a Question
Discussion stats
  • 4 replies
  • 179 views
  • 0 likes
  • 4 in conversation