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:...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.