topic Re: How to output those customers who visited continous 3 times in SAS Programming

How to output those customers who visited continous 3 times

dishant — Wed, 16 Apr 2014 06:35:35 GMT

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.

Re: How to output those customers who visited continous 3 times

Kurt_Bremser — Wed, 16 Apr 2014 07:22:38 GMT

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;

Re: How to output those customers who visited continous 3 times

dishant — Wed, 16 Apr 2014 07:34:59 GMT

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.

Re: How to output those customers who visited continous 3 times

Kurt_Bremser — Wed, 16 Apr 2014 07:50:23 GMT

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;

Re: How to output those customers who visited continous 3 times

RW9 — Wed, 16 Apr 2014 08:25:52 GMT

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;

Re: How to output those customers who visited continous 3 times

dishant — Wed, 16 Apr 2014 08:33:10 GMT

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.

Re: How to output those customers who visited continous 3 times

dishant — Wed, 16 Apr 2014 09:10:08 GMT

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

Re: How to output those customers who visited continous 3 times

SKK — Wed, 16 Apr 2014 09:17:51 GMT

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;

Re: How to output those customers who visited continous 3 times

dishant — Wed, 16 Apr 2014 09:19:11 GMT

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

Re: How to output those customers who visited continous 3 times

UrvishShah — Wed, 16 Apr 2014 09:20:56 GMT

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

Re: How to output those customers who visited continous 3 times

SKK — Wed, 16 Apr 2014 09:24:34 GMT

Hi dishant ,

Run the program it gives the exact output you wanted.

Re: How to output those customers who visited continous 3 times

dishant — Wed, 16 Apr 2014 09:30:56 GMT

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.

Re: How to output those customers who visited continous 3 times

UrvishShah — Wed, 16 Apr 2014 09:51:39 GMT

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

Re: How to output those customers who visited continous 3 times

dishant — Wed, 16 Apr 2014 11:03:30 GMT

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;

Re: How to output those customers who visited continous 3 times

dishant — Wed, 16 Apr 2014 11:06:05 GMT

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

Re: How to output those customers who visited continous 3 times

dishant — Wed, 16 Apr 2014 11:12:46 GMT

Hi Urvish,

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

Re: How to output those customers who visited continous 3 times

SKK — Wed, 16 Apr 2014 11:16:11 GMT

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

Re: How to output those customers who visited continous 3 times

SKK — Wed, 16 Apr 2014 11:31:39 GMT

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.

Re: How to output those customers who visited continous 3 times

UrvishShah — Wed, 16 Apr 2014 11:59:22 GMT

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

Re: How to output those customers who visited continous 3 times

dishant — Wed, 16 Apr 2014 12:01:00 GMT

Hi,

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