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

[Saikiran_Mamidi]

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

[PaigeMiller]

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

Please do not type in ALL CAPITALS.

[ballardw]

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.

[Saikiran_Mamidi]

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

[PaigeMiller]

You still need to explain the logic.

[fdsaaaa]

where cid ne 103....

[mkeintz]

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.

[ballardw]

---

@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)

[fdsaaaa]

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...

[Saikiran_Mamidi]

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)

or

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;

[Patrick]

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.

[Saikiran_Mamidi]

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..