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:...
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: