topic Re: Extracting the first observation on a daily basis in SAS Procedures

Extracting the first observation on a daily basis

mjheever — Wed, 12 Mar 2014 08:03:17 GMT

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

Kurt_Bremser — Wed, 12 Mar 2014 08:18:58 GMT

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;

mjheever — Wed, 12 Mar 2014 08:45:18 GMT

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