Help using Base SAS procedures

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

Reply
Occasional Contributor
Posts: 6

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

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 :-)

Trusted Advisor
Posts: 1,131

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

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

Thanks,
Jag
Super Contributor
Posts: 257

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

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;

Super User
Posts: 6,972

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 3 replies
  • 236 views
  • 1 like
  • 4 in conversation