Hi SAS community :smileygrin:
I'm struggling to find an efficient way to do the following:
Say I have the table
Acc_Nr | Transaction_Date | Transaction_Code | Transaction_Amount |
---|---|---|---|
a1 | 01JUL2012:06:00:00 | 111 | 500 |
a1 | 01JUL2012:12:32:05 | 111 | 400 |
a2 | 01JUL2012:07:23:23 | 222 | 600 |
a1 | 02JUL2012:05:33:20 | 111 | 100 |
a2 | 01JUL2012:13:43:15 | 111 | 400 |
a3 | 02JUL2012:08:32:10 | 222 | 300 |
a3 | 02JUL2012:09:32:30 | 222 | 500 |
So the output I want should be in the form of:
Acc_Nr | Transaction_Date | Transaction_Code | Transaction_Amount |
---|---|---|---|
a1 | 01JUL2012:06:00:00 | 111 | 500 |
a2 | 01JUL2012:07:23:23 | 222 | 600 |
a1 | 02JUL2012:05:33:20 | 111 | 100 |
a3 | 02JUL2012:08:32:10 | 222 | 500 |
I have tried the following code:
proc sort=old;
by Transaction_Date Acc_Nr;
run;
data wanted;
set old;
by Transaction_Date;
if first.Acc_Nr then output;
else delete;
run;
But this does not seem to work (Variable first.Acc_Nr is uninitialized) which is understandable.
Do you have an efficient approach to this problem?
Thank you! :smileygrin:
dansaction_Date
first. and last. are only generated for variables in the "by" list.
Try this:
data int;
set old;
int_day = datepart(Transaction_Date);
run;
proc sort data=int;
by Acc_Nr Transaction_Date;
run;
data wanted (drop=int_day);
set int;
by Acc_Nr int_date;
if first.int_date;
run;
first. and last. are only generated for variables in the "by" list.
Try this:
data int;
set old;
int_day = datepart(Transaction_Date);
run;
proc sort data=int;
by Acc_Nr Transaction_Date;
run;
data wanted (drop=int_day);
set int;
by Acc_Nr int_date;
if first.int_date;
run;
KurtBremser wrote:
first. and last. are only generated for variables in the "by" list.
Thank you for clearing this up Kurt.
The code you provided works perfectly :smileygrin: :smileygrin: :smileygrin:...
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.