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 🙂
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
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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.