New Customer in Each Month

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

New Customer in Each Month

Hi Guys,

I have a data like this,

Month1 Month2  Month3 MOnth4

A          C          D          H

B          D          E          A

C          z          X          X

D          Y          J          V

I want the dataset set like this:

MOnth1      Month2  Month3    Month4

A               Z               E          H

B               Y               X          V

C                                J

D

Basically my objective is to know who are the new customers are adding in each month, who dint appear in any of the previous months, for example if customer D present in first column, it should not appear again in any other column.

Your help is much appreicated !


Accepted Solutions
Solution
‎08-27-2013 10:25 AM
Regular Contributor
Posts: 151

Re: New Customer in Each Month

infile cards;

input month1 $ month2 $ month3 $ month4 $;

array m month:; do month=1 to dim(m); client=m(month); output; end;

drop month1-month4;

cards;

A C D H

B D E A

C z X X

D Y J V

;

 

proc sort data=have; by client month; run;

data have; set have; by client; if first.client; run;

proc sort data=have; by month; run;

proc transpose data=have out=have (drop= _name_);

var client;by month; run;

proc transpose data=have out=want (drop=_name_) prefix=month;

var col:;id month; run;

View solution in original post


All Replies
Solution
‎08-27-2013 10:25 AM
Regular Contributor
Posts: 151

Re: New Customer in Each Month

infile cards;

input month1 $ month2 $ month3 $ month4 $;

array m month:; do month=1 to dim(m); client=m(month); output; end;

drop month1-month4;

cards;

A C D H

B D E A

C z X X

D Y J V

;

 

proc sort data=have; by client month; run;

data have; set have; by client; if first.client; run;

proc sort data=have; by month; run;

proc transpose data=have out=have (drop= _name_);

var client;by month; run;

proc transpose data=have out=want (drop=_name_) prefix=month;

var col:;id month; run;

Super Contributor
Posts: 644

Re: New Customer in Each Month

The main problem you have is with the design of your data, which is why Oleg's solution requires the data to be transposed and then transposed back again.  With your design you have to add an extra column each month (OK in Excel but not the best way of doing things in SAS).  Also, if you have more than 4 customers active in any month, you have to increase the rows.

You should arrange your data like this, so that any number of customers can be added each month, and the month series can continue indefinitely.

MonthCustomer
1A
1B
1C
1D
2C
2D
2Z
2Y
3D
3E
3X
3J
4H
4A
4X
4V

Then a simple query should tell you which was the first month a customer first appeared

Proc SQL ;

     Create table want as

          Select customer

               ,     min (month) as First_Month

          From     have

          Group by customer

     ;

Quit ;

[untested code]

Richard

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 158 views
  • 0 likes
  • 3 in conversation