How to output those customers who visited continous 3 times

[SKK]

If that's the case then as RW9 mentioned you should include the year to know the sequence of months atleast in monyy. format.

My code will only work for jan to dec of a single year.

[dishant]

Hi,

Yeah I want include cust_id 204 in output.So can you Please Let me know What u-pdate needs to be Done..?

[SKK]

Hi dishantn

Pls add year along with the month something like this...

204 Dec2013 12 Paste

204 Jan2014 1 Cloth

204 Feb2014 2 Brush

[UrvishShah]

Hi,

Use this version of code...It will work for more than year data...

proc sort data = cust_dim out = cust_dim1;
  by customer_id month_num;
run;

data cust_dim1;
  set cust_dim1;
  by customer_id;
  _temp1 = dif(month_num);
  if _temp1 LT 0 or _temp1 EQ 1 or _temp1 = 10 then output;
run;

data cust_dim1;
  set cust_dim1;
  by customer_id;
  if first.customer_id then count = 1;
  else count + 1;
  if first.customer_id EQ last.customer_id and count = 1 then delete;
run;

-Urvish

[SKK]

Hi UrvishShah,

Without knowing the  year how will you know the exact sequence of Dec/Jan/Feb...

what if it is Dec2012/Jan2014/Feb2014 ??? Then it would not be consecutive 3 months right..

[UrvishShah]

Yes S K you are ri8...I knew that one too...Every Datamart contains year information for Customer Level data.....

This is just sample code...User can easily manipulate through this code on the basis of year information...

[dishant]

Hi all,

Ok One Small update Let us Consider Year Information Is Also Caputring Then How your Code Will Work And Urvish Your's As well...Can You Pls tell me..

[UrvishShah]

Hi,

Here you go...

proc sort data = cust_dim out = cust_dim1;
  by customer_id month_num year;
run;

data cust_dim1;
  set cust_dim1;
  by customer_id;
  _temp1 = dif(month_num);
  _temp2 = dif(year);
  if (_temp1 LT 0 or _temp1 in (1,10)) and (_temp2 in (0,1,-1)) then output;
run;

proc sql;
   create table cust_dim1(keep = customer_id Month Month_num Product) as
   select *,count(customer_id) as tot
   from cust_dim1
   group by customer_id
   having tot GE 3;
quit;

-Urvish

[SKK]

If your data looks  like this '101 Jan2014 1 Cream' then you can get the desired by the following program,

Data test;

INFORMAT Month MONYY7.;

FORMAT MONTH MONYY7.;

Input customer_id Month  Month_num Product $ ;

Datalines;

101 Jan2014 1 Cream

101 Dec2013 12 Powder

102 Feb2014 2 Cream

102 Mar2014 3 Paste

102 Apr2014 4 cloth

103 Sep2013 9 Cream

103 Jan2014 1 Paste

103 Nov2014 11 Cloth

104 Feb2014 2 Cream

104 Mar2014 3 Paste

104 May2014 5 Cloth

104 Apr2014 4 Brush

204 Dec2013 12 Paste

204 Jan2014 1 Cloth

204 Feb2014 2 Brush

;

Run;

Proc SQL ;

Create Table WANT  as

        select customer_id, Month, Month_num, Product from test where CUSTOMER_ID  in

            (Select Distinct Customer_id From

                 (Select A.* From test as A Inner join test as B ON A.customer_id=B.customer_id

                     Where  INTCK('MONTH',A.MONTH,B.MONTH)=1) AS T

             Group By Customer_id

             Having Count(Customer_id) ge 2)

      order by customer_id,Month;

Quit;

[RW9]

Well the below will give you the result.  However I am thinking your going to come up with problems.  For instance if you have Dec/Jan/Feb, without knowing the year then you can't say they are sequential, and they wouldn't work with my scenario.  What I would suggest is to create a variable - of type DDMMMYYYY - with the month given and the year.  Then default in 01 say, it doesn't matter as we wont use that.  Then use the INTCK function with months option.

Anyways and SQL soloution:

Data test;
     Input customer_id Month $ Month_num Product $ ;
     Datalines;
101 Jan 1 Cream
101 Dec 12 Powder
102 Feb 2 Cream
102 Mar 3 Paste
102 Apr 4 Brush
103 Sep 9 Cream
103 Jan 1 Paste
103 Nov 11 Cloth
104 Feb 2 Cream
104 Mar 3 Paste
104 May 5 Cloth
104 Apr 4 Brush
;
Run;

proc sql;
  create table WANT as
  select  distinct A.CUSTOMER_ID
  from    TEST A
  where   exists(select distinct THIS.CUSTOMER_ID from TEST THIS where THIS.CUSTOMER_ID=A.CUSTOMER_ID and THIS.MONTH_NUM=A.MONTH_NUM+1)
    and   exists(select distinct THIS.CUSTOMER_ID from TEST THIS where THIS.CUSTOMER_ID=A.CUSTOMER_ID and THIS.MONTH_NUM=A.MONTH_NUM+2);
quit;

[SKK]

Try this:

PROC SQL ;

CREATE TABLE WANT  AS select * from test where CUSTOMER_ID  in

  (SELECT DISTINCT CUSTOMER_ID FROM

    (SELECT A.* FROM test AS A INNER JOIN test AS B ON A.CUSTOMER_ID=B.CUSTOMER_ID

     WHERE  A.month_num = B.MONTH_NUM+1  ) AS T

   GROUP BY CUSTOMER_ID

    HAVING COUNT(CUSTOMER_ID) GE 2);

QUIT;

[Neymar]

Hi try this code it may help you ,

proc sort data=test out=temp1;
by customer_id month_num;
run;

data temp2;
retain mon;
set temp1;
by customer_id month_num;
if(first.customer_id = 1 and last.customer_id = 0) then do;
mon=month_num;
output;
end;
if (first.customer_id = 0 and last.customer_id = 0) then do;
if mon=(month_num-1) then output;
mon=month_num;
end;
if (first.customer_id = 0 and last.customer_id = 1) then do;
if mon=(month_num-1) then output;
end;
run;

proc sql;
create table answer as
select customer_id ,Month , Month_num ,Product
from temp2
group by customer_id
having count(customer_id)>=3
order by customer_id, month_num;
quit;