topic Re: Calculations over rows for each customer in SAS Programming

Calculations over rows for each customer

Ronein — Sun, 07 Nov 2021 16:12:43 GMT

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

Re: Calculations over rows for each customer

PaigeMiller — Sun, 07 Nov 2021 15:54:55 GMT

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?

Re: Calculations over rows for each customer

Ronein — Sun, 07 Nov 2021 16:14:49 GMT

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;

Re: Calculations over rows for each customer

PaigeMiller — Sun, 07 Nov 2021 16:30:16 GMT

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.

Re: Calculations over rows for each customer

Ronein — Sun, 07 Nov 2021 16:42:57 GMT

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

Re: Calculations over rows for each customer

PaigeMiller — Sun, 07 Nov 2021 16:47:16 GMT

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.

Re: Calculations over rows for each customer

Ronein — Sun, 07 Nov 2021 16:56:37 GMT

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

Re: Calculations over rows for each customer

yabwon — Sun, 07 Nov 2021 19:02:21 GMT

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

Re: Calculations over rows for each customer

PaigeMiller — Sun, 07 Nov 2021 20:05:02 GMT

@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.

Re: Calculations over rows for each customer

Ronein — Sun, 07 Nov 2021 20:23:20 GMT

Thanks,

May you please explain:

first.scr=rank(score)-64;

why did you write 64?

What does it mean SCR?

Re: Calculations over rows for each customer

Ronein — Sun, 07 Nov 2021 20:25:11 GMT

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)

Re: Calculations over rows for each customer

Ronein — Sun, 07 Nov 2021 20:30:23 GMT

May you please explain the code?

Re: Calculations over rows for each customer

yabwon — Sun, 07 Nov 2021 20:38:17 GMT

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.

Re: Calculations over rows for each customer

yabwon — Sun, 07 Nov 2021 20:45:02 GMT

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;

Re: Calculations over rows for each customer

PaigeMiller — Sun, 07 Nov 2021 20:57:26 GMT

@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?

Re: Calculations over rows for each customer

s_lassen — Mon, 08 Nov 2021 09:11:10 GMT

Here is another solution which is more verbose but easier to understand (I think) than the one suggested by @yabwon :

data want;                                                        
  do until(last.id);                                              
    set have;                                                     
    n_change+(score ne lag(score));  /* the LAG function must be called every time */                             
    by id notsorted;                                              
    if first.id then do;                                          
      score0=score;                                               
      n_change=0;                                                 
      end;                                                        
    end;                                                          
  select(n_change);                                               
    when(0) group=1; /* stays same */                             
    when(1) do;                                                   
      if score>score0 then group=2; /* higher value means worse */
      else group=3; /* score got better */                        
      end;                                                        
    otherwise do;                                                 
      if score>score0 then group=5; /* higher value means worse */
      else group=4; /* score got better or stayed the same */     
      end;                                                        
    end;                                                          
  keep id group n_change score score0;                            
run;

Re: Calculations over rows for each customer

Ksharp — Mon, 08 Nov 2021 13:24:58 GMT

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
;


data want;
change=0;
do until(last.id);
 set have;
 by id score notsorted;
 if first.id then first=score;
 change+first.score;
end;

length classification $ 40;
if change=1 then classification="stay same";
else if change=2 then do; 
                        if first<score then classification='get worse';
                          else  classification='get better';
                      end;
 else if change>2 then do;
                        if first<score then classification='more than 1 change and get worse';
                          else  classification='more than 1 change stay same or better';
                      end;
keep id classification;
run;

Re: Calculations over rows for each customer

Ronein — Mon, 08 Nov 2021 15:06:08 GMT

Thank you,

Data set STATS helped and I am using it to calssify each customer to the correct group.

Thank you again

Re: Calculations over rows for each customer

s_lassen — Tue, 09 Nov 2021 10:07:39 GMT

Hi Ksharp!

Good idea just to use first.score instead of the LAG function.

Only one suggestion: If you set CHANGE=-1 in the beginning of the DATA WANT datastep, you get the actual number of changes, which I think was also one of the data originally wanted.

Re: Calculations over rows for each customer

Ksharp — Tue, 09 Nov 2021 11:54:29 GMT

Agree! 🙂