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;
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;
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;
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;
Thank you for helping!
Such an amazing piece of code.
HHC
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.