topic Re: How do I remove values in current ob based on value in next ob? in SAS Procedures

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

franph — Thu, 11 Jun 2015 23:07:19 GMT

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 🙂

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

Jagadishkatam — Fri, 12 Jun 2015 01:15:13 GMT

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

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

KachiM — Fri, 12 Jun 2015 01:20:29 GMT

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;

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

Kurt_Bremser — Fri, 12 Jun 2015 05:56:08 GMT

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.