topic Re: I WANT TO FIND OUT 2CONSECUTIVE TRANSACTIONS IN ANY 3MONTHS THROUGH OUT YEAR? SUPPOSE 2019? in SAS Programming

I WANT TO FIND OUT 2CONSECUTIVE TRANSACTIONS IN ANY 3MONTHS THROUGH OUT YEAR? SUPPOSE 2019?

Saikiran_Mamidi — Wed, 04 Mar 2020 18:04:47 GMT

DATA HAVE ;

INPUT CID TRASACDATE;

CARDS;

101 01JAN2019

102 22FEB2019

103 24APR2019

104 05JUN2019

105 28AUG2019

106 09OCT2019

107 21NOV2019

;

RUN;

DATA WANT LIKE THIS

CID TRASACDATE

101 01JAN2019

102 22FEB2019

104 05JUN2019

105 28AUG2019

106 09OCT2019

107 21NOV2019

CAN I GET THE CODE PLEASE THANK YOU>>>

Re: I WANT TO FIND OUT 2CONSECUTIVE TRANSACTIONS IN ANY 3MONTHS THROUGH OUT YEAR? SUPPOSE 2019?

PaigeMiller — Wed, 04 Mar 2020 18:13:00 GMT

Please explain the logic used going from the input data to obtain the output data.

Please do not type in ALL CAPITALS.

Re: I WANT TO FIND OUT 2CONSECUTIVE TRANSACTIONS IN ANY 3MONTHS THROUGH OUT YEAR? SUPPOSE 2019?

ballardw — Wed, 04 Mar 2020 18:25:12 GMT

You need to carefully describe you definition for "consecutive transactions". I don't see anything I would consider "consecutive" with a typical meaning of "one right after the other" . Consecutive to 01JAN2019 would typically be 02JAN2019.

"Any 3 months" is extremely vague as well. Do you mean within a 3 month period starting at some value such as the first encountered?

I really cannot see why the 24Apr2019 value would be removed as it is less than 3 months after 22Feb2019.

Since you some sort of ID variable(?) (CID) you need to describe how those values impact the rule.

It may help to provide a more complex example where multiple records are excluded and describe why each one is excluded.

Re: I WANT TO FIND OUT 2CONSECUTIVE TRANSACTIONS IN ANY 3MONTHS THROUGH OUT YEAR? SUPPOSE 2019?

Saikiran_Mamidi — Wed, 04 Mar 2020 18:41:33 GMT

sorry bit wrong in the data...

Given Data

cid tscdate

101 01jan2019

102 14mar2019

103 15jun2019

104 17sep2019

105 20oct2019

106 25dec2019

Required Data

cid tscdate

101 01jan2019

102 14mar2019

104 17sep2019

105 20oct2019

106 25dec2019

Re: I WANT TO FIND OUT 2CONSECUTIVE TRANSACTIONS IN ANY 3MONTHS THROUGH OUT YEAR? SUPPOSE 2019?

PaigeMiller — Wed, 04 Mar 2020 18:48:30 GMT

You still need to explain the logic.

Re: I WANT TO FIND OUT 2CONSECUTIVE TRANSACTIONS IN ANY 3MONTHS THROUGH OUT YEAR? SUPPOSE 2019?

fdsaaaa — Wed, 04 Mar 2020 18:50:01 GMT

where cid ne 103....

Re: I WANT TO FIND OUT 2CONSECUTIVE TRANSACTIONS IN ANY 3MONTHS THROUGH OUT YEAR? SUPPOSE 2019?

mkeintz — Wed, 04 Mar 2020 18:57:04 GMT

You want to delete any record that is exactly 3 months or more away from both the preceding and following records, right? Otherwise keep the record if it is less-than 3 months away from one of its neighbors:

data gavel;
  input cid   tscdate :date9.;
  format tscdate date9.;
datalines;
101  01jan2019
102  14mar2019
103 15jun2019
104 17sep2019
105 20oct2019
106 25dec2019
;
data want (drop=_:);
  merge gavel
        gavel (firstobs=2 keep=tscdate rename=(tscdate=_nxt_dt));
  if min(intck('month',lag(tscdate),tscdate,'continuous')
        ,intck('month',tscdate     ,_nxt_dt,'continuous')
        ) <3;
run;

I use the LAG function to get the date value of the preceding record, and the "firstobs=2" parameter to get the date value of the following record.

Note the use the 'continuous' in the INTCK function that count months in the way you want.

Re: I WANT TO FIND OUT 2CONSECUTIVE TRANSACTIONS IN ANY 3MONTHS THROUGH OUT YEAR? SUPPOSE 2019?

ballardw — Wed, 04 Mar 2020 19:53:39 GMT

@fdsaaaa wrote:

where cid ne 103....

Has nothing to do with the values of any of the transaction dates. So is the requirement to remove records where CID = 103 or is that just an accident that CID needs to be removed because of a date?

The rule involving the date is NOT described by using CID.

If the criteria is actually only to remove CID=103:

Data want;

set have;

where cid ne 103;

run;

(or maybe ne '103', can not tell if the variable is character or numeric from the posts)

Re: I WANT TO FIND OUT 2CONSECUTIVE TRANSACTIONS IN ANY 3MONTHS THROUGH OUT YEAR? SUPPOSE 2019?

fdsaaaa — Wed, 04 Mar 2020 20:27:23 GMT

sorry - that was a tongue in cheek answer which solved %100 of the sample data provided , but perhaps indicated the problem description needed elaboration . too subtle...

Re: I WANT TO FIND OUT 2CONSECUTIVE TRANSACTIONS IN ANY 3MONTHS THROUGH OUT YEAR? SUPPOSE 2019?

Saikiran_Mamidi — Sat, 07 Mar 2020 03:48:54 GMT

Here is the clear explanation...

I'm having data like this

JAN

FEB

MAR

APR

MAY

JUN

JUL

AUG

SEP

OCT

NOV

DEC

FOR EVERY 3 CONSECUTIVE MONTH THE CUSTOMER WHO VISITED TWICE in ANY THREE MONTHS i.e

JAN FEB MAR------------->> In this three consecutive months customer who has visited twice in any months(means jan feb or feb mar or jan mar)

FEB MAR APR------------->> " " " " " (means feb mar or mar apr or feb apr)

....

like that we will get so on..upto oct nov dec.

Here i want to retrieve the records who visited twice in any 3 consecutive months;

Re: I WANT TO FIND OUT 2CONSECUTIVE TRANSACTIONS IN ANY 3MONTHS THROUGH OUT YEAR? SUPPOSE 2019?

Patrick — Sat, 07 Mar 2020 04:24:28 GMT

Please provide sample data in the form of a working SAS data step - the way @mkeintz created sample data here. Then show us exactly how the desired result should look like. In doing so we're likely in a better place to understand the logic you're after (because looking at the data will help us to better understand your explanations).

Based on what you've told us so far the solution @mkeintz posted looks like the answer.

In the data you've posted so far:

I can't see what in your data identifies a customer (like a customer Id or something). You've got in your initial post a variable CID - but this one has unique values so it rather look like a transaction id than a customer id.

Re: I WANT TO FIND OUT 2CONSECUTIVE TRANSACTIONS IN ANY 3MONTHS THROUGH OUT YEAR? SUPPOSE 2019?

Saikiran_Mamidi — Sat, 07 Mar 2020 06:56:32 GMT

Cid date

101 01jan2019

102 02jan2019

103 02jan2019

104 01feb2019

105 02feb2019

101 05feb2019

106 07feb2019

108 01mar2019

104 05mar2019

107 01apr2019

109 02may2019

106 03may2019

102 04may2019

109 05jun2019

the above data we have

i want customer who visited twice in any consecutive 3months.It means customer who visited 2times in (jan feb mar) .means in three months customer need to visit atleast 2times.

for the above who visited twice in three months is

cid

101

104

109

but in data we have many observations....

for example i have given few records to underdstand senario..