## New Customer in Each Month

Solved
Occasional Contributor
Posts: 12

# 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;

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.

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

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.