That proc transpose step is very slick. The only issue I am having applying it to the actual data is that I have 50+ columns that the data is NOT grouped by. The proc transpose step eliminates these columns. I can join these columns back in, but the logic may be a bit challenging to get exactly what is needed. I tried modifying your original solution using merge, but I added a forward/backward dummy variable, similar to what you created for allowed: 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 $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 sort data=have;
by Field1 Field2 Allowed_Amount;
run;
data have;
set have;
by Field1 Field2 Allowed_Amount;
if first.Allowed_Amount then dummy=1;
else dummy+1;
run;
proc sql;
create table test as
select * from have
order by 1,2,abs(Allowed_Amount),dummy;
quit;
data test_mod (rename=(Allowed_Amount=Allowed_Amount_ dummy=dummy_));
set test; run;
data want (keep=Field1 Field2 Allowed_Amount);
merge test (in=t)
test_mod (firstobs=2);
lag_Allowed_Amount=lag(Allowed_Amount);
lag_dummy=lag(dummy);
Lag_Field1=lag(field1);
if t;
if Lag_Field1^=field1 then call missing(lag_Allowed_Amount);
if lag_dummy^=dummy then call missing (lag_dummy);
if dummy=dummy_ and sum(Allowed_Amount,Allowed_Amount_)=0 then delete;
if dummy=lag_dummy and sum(Allowed_Amount,lag_Allowed_Amount)=0 then delete;
run; This provides me with the appropriate remaining allowed amounts, but field1 and field2 have shifted down from merging with the "forward a row" table. The last $151 row should still have A1/B2. Do you know a fix to this issue, using this approach? I'm worried the problem will compound and simple shift will not be enough when applied to the large data set.
... View more
