BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
scb
Obsidian | Level 7 scb
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

@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     

View solution in original post

10 REPLIES 10
Kurt_Bremser
Super User

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
Obsidian | Level 7 scb
Obsidian | Level 7

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
Obsidian | Level 7 scb
Obsidian | Level 7

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
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7 scb
Obsidian | Level 7

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

@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
Tourmaline | Level 20

@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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 998 views
  • 1 like
  • 4 in conversation