SELECT DISTINCT doesn't work. What could I do?

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

SELECT DISTINCT doesn't work. What could I do?

[ Edited ]

Hello there!

 

I work at a bank and we have more or less 5 contact channels for customer complaints. I want to understand every single journey a customer may go through and measure how good those contact channels are at retaining the customer. For example,  see the table below:

 

CUSTOMERCHANNELS
1A
1A
1B
1A
1C
1C
1C
1D
1D
2C
2C
2C
2D
2E
2D

 

 I would to program SAS to tell me customer #1 has not gone through 4 channels (A-B-C-D) but 5 channels (A-B-A-C-D), as well as customer #2 has not gone through 3 channels (C-D-E), but 4 channels (C-D-E-D). My desired output is like:

 

CUSTOMERCHANNELSCHANNEL ORDER
1A1
1A1
1B2
1A3
1C4
1C4
1C4
1D5
1D5
2C1
2C1
2C1
2D2
2E3
2D4

 

I am beginner in SAS programming. I don't know SAS enough to code something like this, but definetely it is my first step to intermediate-level coding and your help is going to be tremendous contribuition for me!

 

Thanks in advance!

best regards!


Accepted Solutions
Solution
‎09-26-2015 11:15 PM
Respected Advisor
Posts: 4,606

Re: SELECT DISTINCT doesn't work. What could I do?

Use BY-processing like this:

 

data have;
input CUSTOMER	CHANNEL $;
datalines;
1	A
1	A
1	B
1	A
1	C
1	C
1	C
1	D
1	D
2	C
2	C
2	C
2	D
2	E
2	D
;

data want;
set have; by customer channel notsorted;
if first.customer then channel_order = 0;
if first.channel then channel_order + 1;
run;

proc print data=want noobs; run;
PG

View solution in original post


All Replies
Solution
‎09-26-2015 11:15 PM
Respected Advisor
Posts: 4,606

Re: SELECT DISTINCT doesn't work. What could I do?

Use BY-processing like this:

 

data have;
input CUSTOMER	CHANNEL $;
datalines;
1	A
1	A
1	B
1	A
1	C
1	C
1	C
1	D
1	D
2	C
2	C
2	C
2	D
2	E
2	D
;

data want;
set have; by customer channel notsorted;
if first.customer then channel_order = 0;
if first.channel then channel_order + 1;
run;

proc print data=want noobs; run;
PG
New Contributor
Posts: 4

Re: SELECT DISTINCT doesn't work. What could I do?

Excellent! You just made my day! Thank you very much!!!
☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 406 views
  • 1 like
  • 2 in conversation