topic Re: Data set's last. variable not working as intended? in SAS Programming

Data set's last. variable not working as intended?

SasDewd — Thu, 21 Mar 2019 20:56:31 GMT

Hello,

I have a data set full of orders.

The data set has 150+ columns and when I update it every day some duplicates will be introduced.

I need to remove those duplicates (based on order_id) and keep the record with the most recent update_date.

The following code works most of the time but I just noticed there are duplicates that the code for some reason is not removing and it does not make any sense.

Here is my code:

proc sort data=Orders out=Orders;
by order_id update_date;
run;

data Orders;
set Orders;
by order_id update_date;
if last.update_date;
run;

What am I missing here and why is this working for some orders but not the others.

I've been staring at one particular order for the past 2hrs and it's the same exact order_id and the update_date is clearly different yet the code returns both records!?

Re: Data set's last. variable not working as intended?

Astounding — Thu, 21 Mar 2019 21:01:20 GMT

Your subsetting statement is incorrect:

if last.update_date;

It should be:

if last.order_id;

Also notice, this is dangerous:

data orders;

set orders;

...........

If your logic is wrong, you can destroy your data set ORDERS. Safer:

data orders2;

set orders;

..............

Re: Data set's last. variable not working as intended?

SasDewd — Thu, 21 Mar 2019 21:04:42 GMT

Hmm..OK...I will try that.

But why?

If I have 2 records:

Order_Id Update_date

100 1/1/2019

100 2/1/2019

So I want to keep the one from 2/1/2019 why would I say if last.order_id and not last.update_date??

Just trying to understand.

Second part makes perfect sense..I will change that.

Thank you for your answer @Astounding !!

Re: Data set's last. variable not working as intended?

SasDewd — Thu, 21 Mar 2019 21:06:45 GMT

I forgot to mention...especially since the code I had was removing duplicates as I was hoping it would...just not all of them

Re: Data set's last. variable not working as intended?

sotojcr — Thu, 21 Mar 2019 21:10:57 GMT

proc sort data=Orders;
by order_id descending update_date;
run;

proc sort data=Orders nodupkey;
by order_id;
run;

Re: Data set's last. variable not working as intended?

ballardw — Thu, 21 Mar 2019 21:40:28 GMT

@SasDewd wrote:

Hello,

I have a data set full of orders.

The data set has 150+ columns and when I update it every day some duplicates will be introduced.

I need to remove those duplicates (based on order_id) and keep the record with the most recent update_date.

The following code works most of the time but I just noticed there are duplicates that the code for some reason is not removing and it does not make any sense.

Here is my code:

proc sort data=Orders out=Orders;
by order_id update_date;
run;

data Orders;
set Orders;
by order_id update_date;
if last.update_date;
run;

What am I missing here and why is this working for some orders but not the others.

I've been staring at one particular order for the past 2hrs and it's the same exact order_id and the update_date is clearly different yet the code returns both records!?

You may need to consider how you are "updating" you data. I sounds like you might be APPENDING data. Either with Proc Append or using a SET statement with two data sets, the master and the "new" data.

If you want a single record for ORDER_ID perhaps you need to use a data step UPDATE statement.

Re: Data set's last. variable not working as intended?

Astounding — Thu, 21 Mar 2019 21:47:35 GMT

LAST.ORDER_ID is true when you hit the last observation for an ORDER_ID. Those are the observations you are looking for.

LAST.UPDATE_DATE is true when you hit the last observation for an UPDATE_DATE. That would select one observation per date.