How to output those customers who visited continous 3 times

[dishant]

Hi,

My data is like below.

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;

Now on Upon data I want those customer_id who had visited Consitative 3 or more times.

Your help will mean a lot. Thanks In Advance.

regards,

Dishant Parikh.

[Kurt_Bremser]

data upon (keep=customer_id number);

set test;

by customer_id;

if first.customer_id then number = 0;

number + 1;

if last.customer_id and number ge 3 then output;

run;

[dishant]

Hi,

Thanks for Your assistance and help.

But as per requirment customer_id 103 shouldn't come because he didn't visited 3 months consicative.

I want those customer_id who visited 3 or more times consicative (Eg. 102,104) with all moths in which he visited.

your help will helpful me.Thanks In advance.

[Kurt_Bremser]

OK, next try

proc sort data=test;

by customer_id month_num;

run;

data upon (keep=customer_id);

set test;

retain

  number

  old_m

;

by customer_id;

if first.customer_id

then do;

  old_m = month_num - 1;

  number = 0;

end;

if month_num = old_m + 1

then number + 1;

else number = 0;

old_m = month_num;

if number = 3 then output;

run;

[dishant]

Hi,

I must say well try.This Solution takes me another level of thinking.

One little modification still required to achevie goal i.e. Output should to be each Customer_id with each Month With each product.

If this will done itll achieve the goal.

As per req ouput shuld to be shown below.

Customer_id Month Month_num Product

102 Feb 2 Cream

102 Mar 3 Paste

102 Apr 4 Brush

104 Feb 2 Cream

104 Mar 3 Paste

104 Apr 4 Brush

104 May 5 Cloth

Can you help on this last note.

[dishant]

As Above mention output SQL is not giving.Can you pls help me on to Acheive Above output.

[dishant]

My requirment is who Visited CONTINOUS 3 or more months those customer_id with  thier respective months should to come to output.

[SKK]

Hi dishant ,

Run the program it gives the exact output you wanted.

[dishant]

Hi SK K,

I Would Say Big thank you to you..Its exactly maching Which Req Want.

Your Solution Takes New Lavel of thinking.Thank a lot Onnce Again.

Hi Urvish,

I want Those Customer_id Which Had Visited Contious 3 months Not Total 3 months.Can you pls Note that Give Solution Like SK K Did and Give me In Data step.

Thanks to all your help Meas A lot.And I Learn New thiongs From All Of You.

[UrvishShah]

I should see previous comments...Anyways this is exactly what you want to achive...

data cust_dim1;
  set cust_dim1;
  by customer_id;
  _temp1 = dif(month_num);
  if _temp1 LT 0 or _temp1 EQ 1 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

[dishant]

Hi SK K,

If Below The Case Then Specifiy Code Is Not Working.Can You pls Help It.

Data cust_dim1;

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 cloth

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

204 Dec 12 Paste

204 Jan 1 Cloth

204 Feb 2 Brush

;

Run;

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(Coustmer_id) ge 2)

order by customer_id,Month_num;

Quit;

[dishant]

Above Dataset name is Test ,Pls Make Update and  Solve it.

[SKK]

Hi... what exactly is your query??? Do you want to include cust_id 204 in output???

[UrvishShah]

Here is my try...

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

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

proc sql;
   create table cust_dim as
   select a.*
   from cust_dim as a,
        cust_dim1 as b
   where a.customer_id = b.customer_id;
quit;

-Urvish

[dishant]

Hi Urvish,

In You Code First Month For Resoective Customer_id Is not Dispalying.Can You Pls Confirm It.