topic Re: SAS query in SAS Programming

SAS query

scb — Tue, 10 Jan 2017 09:42:06 GMT

Dataset_A

Cust Order_DT TRANSACTION_STATUS

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.

Re: SAS query

Kurt_Bremser — Tue, 10 Jan 2017 09:52:08 GMT

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.

Re: SAS query

scb — Tue, 10 Jan 2017 10:10:16 GMT

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.

Re: SAS query

scb — Tue, 10 Jan 2017 10:14:43 GMT

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?

Re: SAS query

RW9 — Tue, 10 Jan 2017 10:19:16 GMT

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;

Re: SAS query

Kurt_Bremser — Tue, 10 Jan 2017 10:24:08 GMT

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;

Re: SAS query

scb — Tue, 10 Jan 2017 10:42:08 GMT

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

Re: SAS query

RW9 — Tue, 10 Jan 2017 11:22:21 GMT

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.

Re: SAS query

Kurt_Bremser — Tue, 10 Jan 2017 11:50:59 GMT

@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

Re: SAS query

PeterClemmensen — Tue, 10 Jan 2017 11:55:58 GMT

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

Re: SAS query

Kurt_Bremser — Tue, 10 Jan 2017 12:01:51 GMT

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.