You are correct, if the rows alternate positive, negative, positive then all are deleted, which I am trying to avoid. This is also an issue in my original dataset, as the allowed amounts can be in any order. I attempted to control for this by first ordering by 1,2,allowed_amount and then ordering by 1,2,abs(allowed_amount) but it appears the rows revert back to their original order? I am unsure why this is happening: proc sql;
create table test1 as
select * from have
order by 1,2,Allowed_Amount;
quit;
proc sql;
create table test2 as
select * from test1
order by 1,2,abs(Allowed_Amount);
quit;
/*
test2 Results:
Field1 Field2 Allowed_Amount
A1 B2 $151
A1 B2 $-151
A1 B2 $151
A1 B2 $220
C1 D2 $25
C1 D2 $-84
C1 D2 $84
C1 D2 $111
*/ When this happens, then all rows are cancelled. I have the same issue with my actual data set. When the rows are manually sorted to positive, positive, negative then the code gives the desired results. My issue is that there can be any number of rows that need to be cancelled. So this logic works with two positives and a negative, but fails to cancel all rows with two positives and two negatives. For example: data have;
input Field1 :$8. Field2:$8. Allowed_Amount:dollar8.;
format Allowed_Amount dollar8.;
datalines;
A1 B2 $151.00
A1 B2 -$151.00
A1 B2 $151.00
A1 B2 $220.00
A1 B2 -$151.00
C1 D2 $84.00
C1 D2 $111.00
C1 D2 $25.00
C1 D2 -$84.00
;
run;
proc sql;
create table test1 as
select * from have
order by 1,2,Allowed_Amount;
quit;
proc sql;
create table test2 as
select * from test1
order by 1,2,abs(Allowed_Amount);
quit;
/*
test2 results:
Field1 Field2 Allowed_Amount
A1 B2 $151
A1 B2 $151
A1 B2 $-151
A1 B2 $-151
A1 B2 $220
C1 D2 $25
C1 D2 $-84
C1 D2 $84
C1 D2 $111
*/ So for three rows, the modified order works with your code. With four rows, you original code that orders by abs(allowed_amount) gives me what I'm looking for. I am having issues adjusting the code to fit all combinations of canceling rows, in my original data set. For example: data have;
input Field1 :$8. Field2:$8. Allowed_Amount:dollar8.;
format Allowed_Amount dollar8.;
datalines;
A1 B2 $151.00
A1 B2 -$151.00
A1 B2 $151.00
A1 B2 $220.00
A1 B2 -$151.00
A1 B2 $85.00
A1 B2 $151.00
C1 D2 $84.00
C1 D2 $111.00
C1 D2 $25.00
C1 D2 -$84.00
;
run;
proc sql;
create table test1 as
select * from have
order by 1,2,abs(Allowed_Amount);
quit;
test1 results:
A1 B2 $85
A1 B2 $151
A1 B2 $-151
A1 B2 $151
A1 B2 $151
A1 B2 $-151
A1 B2 $220
C1 D2 $25
C1 D2 $-84
C1 D2 $84
C1 D2 $111
Want results:
A1 B2 $85
A1 B2 $220
C1 D2 $25
C1 D2 $111
... View more