BookmarkSubscribeRSS Feed
Manikanta
Calcite | Level 5

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 

4 REPLIES 4
HB
Barite | Level 11 HB
Barite | Level 11

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?

Manikanta
Calcite | Level 5

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 .

andreas_lds
Jade | Level 19

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

 

 

Ksharp
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 934 views
  • 0 likes
  • 4 in conversation