How do I remove values in current ob based on value in next ob?

[franph]

Hi

I would like to code this..

if trandate in current ob = trandate in next ob then delete dailybal, accringbal & aggregatebal  from current ob

sample input dataset....

trandate          amount     dailybal     accruingbal     aggregatebal    

28/01/2015                                        3451.76            3451.76

29/01/2015                                        3451.76            6903.52  

30/01/2015     -50.00       -50.00        3401.76            10305.28       .       

31/01/2015                                         3401.76            13707.04                      

01/02/2015      56.60       223.59         3625.35           117332.39

01/02/2015      166.99     223.59       3625.35           117332.39

02/02/2015                                         3625.35          20957.74

desired result

trandate          amount     dailybal     accruingbal     aggregatebal    

28/01/2015                                        3451.76            3451.76

29/01/2015                                        3451.76            6903.52  

30/01/2015     -50.00       -50.00        3401.76            10305.28       .       

31/01/2015                                         3401.76            13707.04                      

01/02/2015      56.60      

01/02/2015      166.99     223.59       3625.35           117332.39

thanks for your help 🙂

[Jagadishkatam]

Please try

data want;

set have;

by  trandate;

retain seq;

if first.trandate then seq=1;

else seq+1;

run;

data want2;

merge want(in=a) want(in=b keep=seq rename=(seq=flag2) firstobs=2);

if flag2>1 then call missing (dailybal , accruingbal, aggregatebal);

run;

Thanks,

Jag

[KachiM]

Try this too.

data have;

input trandate ddmmyy10. amount dailybal accruingbal aggregatebal;   

datalines;

28/01/2015        .     .         .     3451.76     3451.76

29/01/2015        .     .         .     3451.76     6903.52

30/01/2015     -50.00 -50.00   3401.76  10305.28       .  

31/01/2015        .      .       .       3401.76   13707.04

01/02/2015      56.60  223.59  3625.35 117332.39      .

01/02/2015     166.99  223.59  3625.35 117332.39      .

02/02/2015        .       .       .      3625.35    20957.74

;

run;

data want;

   set have; set have(firstobs = 2 keep = trandate rename = (trandate = tran));

   if trandate = tran then call missing(dailybal,accruingbal, aggregatebal);

drop tran;

run;

proc print data = want;

format trandate ddmmyy10.;

run;

[Kurt_Bremser]

Sort the dataset in reverse order, then you can use the lag() function to access data that was "later" in your original sequence.

After that, re-sort to your original order.