Solved
New Contributor
Posts: 4

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

 CUSTOMER CHANNELS 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

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:

 CUSTOMER CHANNELS CHANNEL ORDER 1 A 1 1 A 1 1 B 2 1 A 3 1 C 4 1 C 4 1 C 4 1 D 5 1 D 5 2 C 1 2 C 1 2 C 1 2 D 2 2 E 3 2 D 4

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!

best regards!

Accepted Solutions
Solution
‎09-26-2015 11:15 PM
Posts: 5,543

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

All Replies
Solution
‎09-26-2015 11:15 PM
Posts: 5,543

## 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 and locked.