topic Re: find min and min within two unique values in SAS Programming

find min and min within two unique values

AKHILA — Tue, 28 May 2019 10:03:17 GMT

in the following dataset i want to derive a new variable 'TYPE' in which the max and min of val2 within id for each val1 should be populated like

ID VAL1 VAL2
001 HT 170
001 HT 170
001 HT 170
001 WT 56
001 WT 78
001 WT 91
001 TM 32
001 TM 31
001 TM 27
001 BP 120
001 BP 111
001 BP 178
002 HT 169
002 HT 169
002 HT 169
002 WT 34
002 WT 89
002 WT 90

if the val2 is constant for any val2(here HT) I have to keep only one record for that .the output should be like;

ID VAL1 VAL2 TYPE
001 HT 170
001 WT 56 min
001 WT 78
001 WT 91 max
001 TM 32 max
001 TM 31
001 TM 27 min
001 BP 120
001 BP 111 min
001 BP 178 max
002 HT 169
002 WT 34 min
002 WT 89
002 WT 90 max

please help.

Re: find min and min within two unique values

PeterClemmensen — Tue, 28 May 2019 10:33:54 GMT

Does the order of the observations matter? Are we allowed to sort the data?

Re: find min and min within two unique values

Amir — Tue, 28 May 2019 10:35:00 GMT

Does the following give you what you want:

data have;
   input id     $3.
         val1 : $2.
         val2 :  8.
   ;

   datalines;
001 HT 170
001 HT 170
001 HT 170
001 WT 56
001 WT 78
001 WT 91
001 TM 32
001 TM 31
001 TM 27
001 BP 120
001 BP 111
001 BP 178
002 HT 169
002 HT 169
002 HT 169
002 WT 34
002 WT 89
002 WT 90
;

/* process using double DOW loop */
data want(drop = minimum maximum done_constant);
   /* work out min and max for this val1 group */
   do until(last.val1);
      set have;
      by id val1 notsorted;

      minimum = min(minimum,val2);
      maximum = max(maximum,val2);
   end;

   /* work out if this val1 record is constant, else assign 'type' */
   do until(last.val1);
      set have;
      by id val1 notsorted;

      if minimum = maximum then
      do;
         if not done_constant then
         do;
            output;
            done_constant = 1;
         end;
      end;
      else
      do;
         type = ifc(val2 = minimum,'min',ifc(val2 = maximum,'max',''));
         output;
      end;
   end;
run;

Amir.

Re: find min and min within two unique values

Oligolas — Tue, 28 May 2019 14:17:58 GMT

DATA want(drop=min);
   set have end=last;
   by id val1 notsorted;
   retain min .;
   if first.val1 then do;
      type='min';
      min=val2;
   end;
   if last.val1 then do;
      type='max';
      if min eq val2 then call execute('PROC SQL;UPDATE want SET type="" WHERE id eq "'||strip(id)||'" AND val1 eq "'||strip(val1)||'" AND val2 eq '||strip(put(val2,best.))||';QUIT;');
   end;
RUN;

PROC SORT data=want nodupkey;by id val1 val2 type; RUN;