topic Grouping rows of the same sign into 1 in SAS Programming

Grouping rows of the same sign into 1

hhchenfx — Tue, 20 Dec 2022 03:21:20 GMT

Hi Everyone,

My data below has id, sign (+1/-1) and value and already in order of time.

The sign supposed to be in the +1 -1 +1 -1 sequence. However, there are rows having the same sign (+1 +1 +1).

Thus I need to keep only 1 row to restore the right sequence rule.

So for +1+1+1..+1, I only keep the row with the highest value. This one below, I want to keep 2 3 1 8

2 1 1 0
2 2 1 5
2 3 1 8

And for -1-1-1...-1, I want to keep the row with the lowest value. This one below, I want to keep 1 5 -1 5

1 4 -1 8
1 5 -1 5
1 6 -1 7

Can you help me with this problem?

Many thanks,

HHC

data have;
input id time sign value;
datalines;
1 1 1 9
1 2 -1 -8
1 3 1 4
1 4 -1 8
1 5 -1 5
1 6 -1 7
2 1 1 0
2 2 1 5
2 3 1 8
2 4 -1 5
2 5 1 4
2 6 -1 3
2 7 1 5
;run;

Re: Grouping rows of the same sign into 1

Tom — Tue, 20 Dec 2022 04:47:06 GMT

You can use BY group processing to define the alternating groups.

Then you can sort. To make so you can use one sort for both types of groups just multiply the sign and the value. Then for both you want the maximum value since the negative number with the smaller absolute value is larger than a negative number with a larger absolute value.

data want;
  set have;
  by id sign notsorted;
  if first.id then group=0;
  group + first.sign;
  number = sign * value;
run;

proc sort;
  by id group number ;
run;

data want;
  set want ;
  by id group;
  if first.group;
run;

Re: Grouping rows of the same sign into 1

Ksharp — Tue, 20 Dec 2022 08:48:27 GMT

data have;
input id time sign value;
datalines;
1 1 1 9
1 2 -1 -8
1 3 1 4
1 4 -1 8
1 5 -1 5
1 6 -1 7
2 1 1 0
2 2 1 5
2 3 1 8
2 4 -1 5
2 5 1 4
2 6 -1 3
2 7 1 5
;run;

data want;
min=constant('exactint');max=.;
 do until(last.sign);
   set have;
   by id sign notsorted;
   if value<min then do;min=value;min_time=time;end;
   if value>max then do;max=value;max_time=time;end;
 end;
 if sign=1 then do;value=max;time=max_time;end;
 if sign=-1 then do;value=min;time=min_time;end;
 drop min max min_time max_time;
run;

Re: Grouping rows of the same sign into 1

hhchenfx — Tue, 20 Dec 2022 19:51:10 GMT

Thank you for helping!

Such an amazing piece of code.

HHC