BookmarkSubscribeRSS Feed
dishant
Calcite | Level 5


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.

26 REPLIES 26
Kurt_Bremser
Super User

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
Calcite | Level 5

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

OK, next try Smiley Happy

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
Calcite | Level 5


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
Calcite | Level 5

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

dishant
Calcite | Level 5

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

SKK
Calcite | Level 5 SKK
Calcite | Level 5

Hi

dishant
Calcite | Level 5

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
Fluorite | Level 6

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
Calcite | Level 5

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
Calcite | Level 5

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

SKK
Calcite | Level 5 SKK
Calcite | Level 5

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

UrvishShah
Fluorite | Level 6

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
Calcite | Level 5

Hi Urvish,

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 26 replies
  • 1499 views
  • 2 likes
  • 6 in conversation