Calculations over rows for each customer

[Ronein]

Hello
For each customer there is follow up of maximum 12 months.
(It means that the follow up period can be lower than 12 months but cannot be more than 12 months).
In each period there is information of score (A-best score.....E-worse score).
Score in time 0 is base score and then there is follow up (of maximum 12 months) 
I want to create a new data set (called wanted) with one row for each customer with the following columns:
 
1-Score in base month (time 0)
Expected values:
For ID=1 :base is A
For ID=2 :base is B
For ID=3 :base is C
For ID=4 :base is D
For ID=5 :base is A
 
2-Max score over the follow up period(Follow up is in time 1 till 12)
Expected values:
For ID=1 :Max is A
For ID=2 :Max is D
For ID=3 :Max is C
For ID=4 :Max is E
For ID=5 :Max is E
For ID=6 :Max is C
 
3-Last Score 
For ID=1 :Last is A
For ID=2 :Last is D
For ID=3 :Last is C
For ID=4 :Last is E
For ID=5 :Last is A
For ID=5 :Last is B
 
4-Classify each customer ID for one of the following groups:
Group_1: Customer stay in same score over time(so scores in times 0,1,2,3,4,5,6,7,8,9,10,11,12 are same)
For example: ID 1,3
Group_2: Customer go to worse score and stay there
For example: ID 2
Group_3: Customer go to better score and stay there
For example: ID 6
Group_4: There are at least 2 changes in scores over time and last score is better/same  than score in time 1
For example: ID 5
Group_4: There are at least 2 changes in scores over time and last score is worse than score in time 1
For example: ID 4
 
5-Number of changes in score from time 0 till time 12

[PaigeMiller]

1 12 A2 0 B

What does this row in your input data mean? What is A2? what is the 0 and B on this row?

Shouldn't there be 3 variables (or more?) in your input statement? Can you fix this?

[Ronein]

Please see the raw data.
Data have;
Input ID time score $;
2 0 B
2 1 B
2 2 D
3 0 C
3 1 C
3 2 C
4 0 D
4 1 C
4 2 C
4 3 C
4 4 D
4 5 D
4 6 E
5 0 B
5 1 B
5 2 E
5 3 B
6 0 C
6 1 C
6 2 C
6 3 C
6 4 B
6 5 B
6 6 B
1 0 A
1 1 A
1 2 A
1 3 A
1 4 A
1 5 A
1 6 A
1 7 A
1 8 A
1 9 A
1 10 A
1 11 A
1 12 A
;
Run;

[PaigeMiller]

Your code still doesn't work. I would be delighted if, in the future, you could provide working code instead of expecting us to debug basic code like this.

data have2;
	set have;
	by id notsorted;
	if score='A' then score2=1;
	else if score='B' then score2=2;
	else if score='C' then score2=3;
	else if score='D' then score2=4;
	else if score='E' then score2=5;
	if first.id then first_score=score2;
	if last.id then last_score=score2;
	prevscore2=lag(score2);
	if not first.id and score2^=prevscore2 then change=1;
run;

proc summary data=have2 nway;
	class id;
	var change first_score last_score;
	output out=stats sum=;
run;

From the output data set named STATS, you can create the categories for each ID, which I leave up to you.

[Ronein]

This solution is great but what about the most complicated new field.
Classify each customer ID for one of the following groups:
Group_1: Customer stay in same score over time

Group_2: Customer go to worse score and stay there


Group_3: Customer go to better score and stay there

Group_4: There are at least 2 changes in scores over time and last score is better/same than score in time 0

Group_4: There are at least 2 changes in scores over time and last score is worse than score in time 0

[PaigeMiller]

That's what I left for you to do. From the information in the data set named STATS, you can tell which group each customer is in.

[Ronein]

Please see the wanted classifications:

2 "get worse "
2 0 B
2 1 B
2 2 D
Customer 3 "stay same"
3 0 C
3 1 C
3 2 C
Customer 4 " more than 1 change and get worse "
4 0 D
4 1 C
4 2 C
4 3 C
4 4 D
4 5 D
4 6 E
Customer 5 "more than 1 change stay same or better "
5 0 B
5 1 B
5 2 E
5 3 B
Customer 6 "get better "
6 0 C
6 1 C
6 2 C
6 3 C
6 4 B
6 5 B
6 6 B
Customer 1 "stay same"
1 0 A
1 1 A
1 2 A
1 3 A
1 4 A
1 5 A
1 6 A
1 7 A
1 8 A
1 9 A
1 10 A
1 11 A
1 12 A

[yabwon]

Just for fun, maybe like that:

data have;
Input ID time score $;
cards;
2 0 B
2 1 B
2 2 D
3 0 C
3 1 C
3 2 C
4 0 D
4 1 C
4 2 C
4 3 C
4 4 D
4 5 D
4 6 E
5 0 B
5 1 B
5 2 E
5 3 B
6 0 C
6 1 C
6 2 C
6 3 C
6 4 B
6 5 B
6 6 B
1 0 A
1 1 A
1 2 A
1 3 A
1 4 A
1 5 A
1 6 A
1 7 A
1 8 A
1 9 A
1 10 A
1 11 A
1 12 A
;
run;

/* [EDIT] */
data want;

  do until (last.id);
    set have;
    by id score notsorted;

    if first.id then first.scr=rank(score)-64;
    if last.id  then last.scr =rank(score)-64;

    change + first.score;
  end;
    
    change + -1;
    wb = sign(last.scr-first.scr);
    
    classification = catx(" ", ifc(change, "more than 1 change " !! ifc(wb>0, " get worse", " get better or stay same")
                                         , ifc(wb>0, "get worse", ifc(wb<0, "get better", "stay same"))) );
  output;

  change = 0;
run;

Bart

[Ronein]

Thanks,

May you please explain:

first.scr=rank(score)-64;

why did you write 64?

What does it mean SCR?

[yabwon]

The rank() function returns number of character in ASCII table, rank("A") is 65, rank("B") is 66, etc. so rank("A")-64 gives 1, rank("B")-64 gives 2, etc.

The "first.src" and "last.scr" are just two temporary variables which will be automatically deleted from output from pdv, you can replace them by first_src and last_src and add DROP into the code and you will get the same effect.

B.

[yabwon]

When I thought about it I see it can be even simpler:

data want;

  do until (last.id);
    set have;
    by id score notsorted;

    if first.id then first.scr=rank(score);
    if last.id  then last.scr =rank(score);

    change + first.score;
  end;
    
    change + -1;
    wb = last.scr-first.scr;
    
    classification = catx(" ", ifc(change, "more than 1 change " !! ifc(wb>0, " get worse", " get better or stay same")
                                         , ifc(wb>0, "get worse", ifc(wb<0, "get better", "stay same"))) );
  output;
  change = 0;
run;

B.

[Ronein]

May you please explain the code?

[PaigeMiller]

---

@Ronein wrote:
Please see the wanted classifications:

2 "get worse "
2 0 B
2 1 B
2 2 D

etc.

---

All the information you need to create these categories is now in the data set named STATS. Did you actually look at the data set named STATS?? You should be able to take that information and create the categories in a minute or so. It's a homework assignment for you.

[Ronein]

Thanks,
I thinks that in order to assign to category need to know 2 things:
1- Number of changes
2-First Score VS last score(same/better/worse)

[PaigeMiller]

---

@Ronein wrote:
Thanks,
I thinks that in order to assign to category need to know 2 things:
1- Number of changes
2-First Score VS last score(same/better/worse)

---

You are very consistent at writing words without expressing what your question is. What is your question?

Please answer my earlier question: did you actually look at the data set named STATS? If so, what did you see in there?