SAS query

[scb]

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.

[Kurt_Bremser]

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.

[scb]

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.

[scb]

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?

[Kurt_Bremser]

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;

[RW9]

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;

[scb]

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

[RW9]

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.

[Kurt_Bremser · Accepted solution]

---

@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     

[PeterClemmensen]

@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? 🙂

[Kurt_Bremser]

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.