BookmarkSubscribeRSS Feed
AKHILA
Obsidian | Level 7

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.

3 REPLIES 3
PeterClemmensen
Tourmaline | Level 20

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

Amir
PROC Star

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.

Oligolas
Barite | Level 11
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;
________________________

- Cheers -

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 3 replies
  • 503 views
  • 2 likes
  • 4 in conversation