Dataset_A
Cust Order_DT TRANSACTION_STATUS
123 01JAN2017 NEW
123 01JAN2017 NEW
123 02JAN2017 OLD
124 01JAN2017 NEW
125 01JAN2017 NEW
125 02JAN2017 OLD
To define the transaction status; the first transaction done by every cust is NEW, if there are more than 1 transaction on the same day then all transaction will be tagged as NEW.
My code below but only 1 record tagged as NEW when order_dt is the same (Cust 123, Order_dt =01jan2017)
DATA DATASET_A;
SET DATASET_A;
BY CUST ORDER_DT;
IF FIRST.CUST AND FIRST.ORDER_DT THEN TRANSACTION_STATUS = ‘NEW’;ELSE TRANSACTION_STATUS = ‘OLD’;
RUN;
Anyone can help? Thanks.
@scb wrote:
The codes can't get the desired result.
Offering Id Status Obtained Desired Status
101764 New New
101765 New New
107476 Old Old
107477 New Old
99370 New New
100263 Old Old
100264 New Old
100265 New Old
Then you must have made an error copying the code.
My code (including test data):
data have;
input cust order_dt :date9. offering_id;
format order_dt date9.;
cards;
1100071399 05AUG2016 101764
1100071399 05AUG2016 101765
1100071399 31OCT2016 107476
1100071399 31OCT2016 107477
1300041342 01JUL2016 99370
1300041342 18JUL2016 100263
1300041342 18JUL2016 100264
1300041342 18JUL2016 100265
;
run;
data want;
set have;
by cust order_dt;
retain hold_date;
if first.cust then hold_date = order_dt;
if order_dt = hold_date
then transaction_status = 'NEW';
else transaction_status = 'OLD';
drop hold_date;
run;
proc print noobs data=want;
run;
The result:
offering_ transaction_ cust order_dt id status 1100071399 05AUG2016 101764 NEW 1100071399 05AUG2016 101765 NEW 1100071399 31OCT2016 107476 OLD 1100071399 31OCT2016 107477 OLD 1300041342 01JUL2016 99370 NEW 1300041342 18JUL2016 100263 OLD 1300041342 18JUL2016 100264 OLD 1300041342 18JUL2016 100265 OLD
You need to slightly change the condition:
data dataset_a1;
set dataset_a;
by cust order_dt;
if first.cust or not first.order_dt
then transaction_status = 'NEW';
else transaction_status = 'OLD';
run;
Note my use of the code editor ("little running man" icon) for posting code, and not writing code in capitals.
I attached the dataset; I am sorry that i missed OFFERING ID earlier.
The transaction status for offering id 101764, 101765 and 99370 should be NEW, the rest are OLD.
When I run my earlier codes, the only thing can’t work was when 2 transactions done at the same date but only the first order was tagged as new. (offering id 101764 and 101765). What have I missed from here?
Yeah, I see the problem with the dates after the first one.
So we have to keep the date of the first day:
data dataset_a1;
set dataset_a;
by cust order_dt;
retain hold_date;
if first.cust then hold_date = order_dt;
if order_dt = hold_date
then transaction_status = 'NEW';
else transaction_status = 'OLD';
drop hold_date;
run;
Just to add here, as this is a binary yes/no choice, its a good use for the ifc/ifn functions:
data dataset_a1;
set dataset_a;
by cust order_dt;
transaction_status=ifc(first.cust or not first.order_dt,'NEW','OLD');
run;
The codes can't get the desired result.
Offering Id Status Obtained Desired Status
101764 New New
101765 New New
107476 Old Old
107477 New Old
99370 New New
100263 Old Old
100264 New Old
100265 New Old
Please clarify your logic on why certain things are the way you say they are, your test data:
Cust Offering Id Order_dt Status Obtained Desired Status 1100071399 101764 05AUG2016 New New 1100071399 101765 05AUG2016 New New <- Why is this one new 1100071399 107476 31OCT2016 Old Old 1100071399 107477 31OCT2016 New Old <- And not this one 1100071342 99370 01JUL2016 New New 1100071342 100263 18JUL2016 Old Old <- Why are these all old? 1100071342 100264 18JUL2016 New Old 1100071342 100265 18JUL2016 New Old
So row 2 and 4 for instance, I cannot see any logical reason why, if row 2 is new, why row 4 would not be new? Also I could not see any logical reason why the last three rows would all be old.
@scb wrote:
The codes can't get the desired result.
Offering Id Status Obtained Desired Status
101764 New New
101765 New New
107476 Old Old
107477 New Old
99370 New New
100263 Old Old
100264 New Old
100265 New Old
Then you must have made an error copying the code.
My code (including test data):
data have;
input cust order_dt :date9. offering_id;
format order_dt date9.;
cards;
1100071399 05AUG2016 101764
1100071399 05AUG2016 101765
1100071399 31OCT2016 107476
1100071399 31OCT2016 107477
1300041342 01JUL2016 99370
1300041342 18JUL2016 100263
1300041342 18JUL2016 100264
1300041342 18JUL2016 100265
;
run;
data want;
set have;
by cust order_dt;
retain hold_date;
if first.cust then hold_date = order_dt;
if order_dt = hold_date
then transaction_status = 'NEW';
else transaction_status = 'OLD';
drop hold_date;
run;
proc print noobs data=want;
run;
The result:
offering_ transaction_ cust order_dt id status 1100071399 05AUG2016 101764 NEW 1100071399 05AUG2016 101765 NEW 1100071399 31OCT2016 107476 OLD 1100071399 31OCT2016 107477 OLD 1300041342 01JUL2016 99370 NEW 1300041342 18JUL2016 100263 OLD 1300041342 18JUL2016 100264 OLD 1300041342 18JUL2016 100265 OLD
@Kurt_Bremser off topic, I ran this code and it works, but how come you can use by group processing, when the data is not properly sorted? 🙂
In the example data, the by variables are properly sorted. If they weren't (but the groups were still meaningful), we would have to use the notsorted option in the by statement.
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 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: