create progressive variable by another variable

[boshmers]

Hello, this is my first post here. Thank you in advance for your help

So I have this datset where id_client is the identifier for my clients, n_try is the numer of times i've tryed to contact them for a purchase proposal anche outcome is Y for "accepted", N is "did not accept" and O is for "did not answered the phone"

id_client	n_try	outcome
21	1	O
21	2	N
53	1	O
53	2	O
53	3	Y
17	1	Y
17	2	Y
17	3	N
17	4	O

I want to obtain the outcome

id_client	n_try	outcome	pattern
21	1	O	O
21	2	N	ON
53	1	O	O
53	2	O	OO
53	3	Y	OOY
17	1	Y	Y
17	2	Y	YY
17	3	N	YYN
17	4	O	YYNO

Can you help me?

Thank you!!

[sbxkoenk · Accepted solution]

Hello @boshmers ,

Welcome to the SAS Communities!

Here's AN answer to your question (it's not THE answer as there are always many alternatives to achieve the same).

I don't know if it's important to restore the original sort order of the clients. If so, just let me know ... then we fix that too !

data have0;
input id_client	n_try outcome $;
datalines;
21 1 O
21 2 N
53 1 O
53 2 O
53 3 Y
17 1 Y
17 2 Y
17 3 N
17 4 O
; 

PROC SORT data=have0 out=have1;
 by id_client n_try;
run;

data want;
 LENGTH pattern $ 50;
 set have1;
 retain pattern;
 by id_client;
 if first.id_client then pattern="";
 pattern=strip(pattern) !! ' ' !! strip(outcome);
run;

proc print; run;
/* end of program */

BR,

Koen

[boshmers]

Thank you, it worked perfectly!

[PaigeMiller]

As this is your first post here, please understand that I give this advice to lots of people. Transforming data for distinct outcomes in each row, to something like ON when there are two outcomes, is often a mistake and actually makes further analysis more difficult. I would advise you to leave the data un-transformed, and explain what you are going to do with it, so we can determine good ways to perform the next steps and whether or not this transformation is helpful.