Linking scores made on same account multidimensional array

[smend]

I am trying to figure out how many accounts this individual created to play games from 2013 to 2019.   Also, I am trying to link all the scores that were played in the same account.

The score array indicates the scores in the year 2013 to 2019.  A score of (-1) indicates no scores were recorded in that year. 

Array score {4, 10}      score_2013_1-score_2013_3 score_2015_1-score_2015_3

                                   score_2017_1-score_2017_3 score_2019_1-score_2019_3;

The rank array indicates the rank of the scores (lowest score is 1) in the year 2013 to 2019. A rank (-1) value also indicates no score and no ranking. 

Array rank{4, 3}    rank_2013_1-rank_2013_3 rank_2015_1-rank_2015_3

                             rank_2017_1-rank_2017_3 rank_2019_1-rank_2019_3;

The prevrank array is used to the link the rank of current year to the previous year.  The prevrank rank greater than 0 indicates the rank of the player scored in the previous year on the same account.  If the score and the rank are both greater than 0 and the prevrank is (-1), then this score was recorded on a new account that the individual created. In 2013, all the scores recorded on new accounts.  prevrank_2013_1-prevrank_2013_3 are all going to (-1). 

Array prevrank{4, 3} prevrank_2013_1-prevrank_2013_3 prevrank_2015_1-prevrank_2015_3

                                 prevrank_2017_1-prevrank_2017_3 prevrank_2019_1-prevrank_2019_3 ;

data example;

input

score_2013_1-score_2013_3 score_2015_1-score_2015_3 score_2017_1-score_2017_3 score_2019_1-score_2019_3

rank_2013_1-rank_2013_3 rank_2015_1-rank_2015_3 rank_2017_1-rank_2017_3 rank_2019_1-rank_2019_3

prevrank_2013_1-prevrank_2013_3 prevrank_2015_1-prevrank_2015_3 prevrank_2017_1-prevrank_2017_3 prevrank_2019_1-prevrank_2019_3;

datalines;

1 2 3  -1 -1 -1 11 3 12 17 18 19

1 2 3  -1 -1 -1 2  1 3   1 2 3

-1 -1 -1 -1 -1 -1 3 -1 1 -1 2 3

run;

In the example, this individual recorded three scores in 2019

score_2019_1= 17 and ranked rank_2019_1=1

score_2019_2=18 and ranked rank_2019_2=2

score_2019_3=19 and ranked rank_2019_3=3. 

Linking:

prevrank_2019_1=-1, which indicates the score was recorded on a new account that the individual created in 2019.

prevrank_2019_2= 2, indicates that this was scored on an account which ranked 2nd in the previous year.

prevrank_2019_3= 3,  indicates that this was scored on an account which ranked 3rd in the previous year.   

If the score and rank are all -1 in a year, then the link would continue to the year before where all score and rank are non negatives.

I would like to create an array which will display all the scores made on each account for this individual (the most recent score to the older score).  In the example, the player created 5 accounts from 2013 to 2019 and the scores in each account are:

Account1scores1-Account1scores3 (17)

Account2scores1-Account2scores4 (18, 11, 2)

Account3scores1-Account3scores2 (19, 12, 1)

Account4scores1-Account4scores2 (3)

Account5scores1-Account5scores1 (3)

data want;

input account1score1-account1score3 account2score1-account2score3 account3score1-account3score3 account4score1-account4score3 account5score1-account5score3;

datalines;

17 . . . . 18 11 2 . . 19 12 1 . . 3 . . . . 3 . . . .

run;

Thank you for you feedbacks.

[KachiM]

It will be better you fill the dots(.) with numbers or use 3 values each. It will be easier to run the data step and see your specification. Your problem is not complicated but lacks numbers for the cells.

[ballardw]

---

@smend wrote:

I would like to link an individual scores in multidimensional array.  The array (a) is the scores of individual in each round.  There are four rounds.  The array (b) is the group that this individuals belongs to in the current round.  The array (c) is the group this individuals belonged to in the previous round. I would like to use the array (c) as the link to the previous round group and the previous round score.  The link stops when the array (c) equals -1 or less than 0.

Example: 

data example;
input a1_1-a1_10 a2_1-a2_10 a3_1-a3_10 a4_1-a4_10
b1_1-b1_10 b2_1-b2_10 b3_1-b3_10 b4_1-b4_10
c1_1-c1_10 c2_1-c2_10 c3_1-c3_10 c4_1-c4_10;
datalines;
1 2 3 -1 -1 -1 -1 -1 -1 -1 12 11 10 -1 -1 -1 -1 -1 -1 -1 20 21 22 -1 -1 -1 -1 -1 -1 -1 30 31 32 -1 -1 -1 -1 -1 -1 -1
1 2 3 -1 -1 -1 -1 -1 -1 -1 3 2 1 -1 -1 -1 -1 -1 -1 -1 1 2 3 -1 -1 -1 -1 -1 -1 -1 1 2 3 -1 -1 -1 -1 -1 -1 -1
-1 -1 -1 -1 -1 -1 -1 -1 -1 -1 1 3 -1 -1 -1 -1 -1 -1 -1 -1 -1 2 1 -1 -1 -1 -1 -1 -1 -1 3 2 1 -1 -1 -1 -1 -1 -1 -1
run;

In the fourth round, this individual score of 30 (a4_1) and belonged to group 1 (b4_1).  The c4_1 equals 3 suggest this person belonged to 3rd group in the previous last round.  The score of the 3rd group (b3_3) in the previous round equals 22 (a3_3).  This belonged to 1st group in the round before (c3_3).

4th round:  b4_1 equals 1.  a4_1 equals 30.  c4_1 equals 3.

3rd round: b3_3 equals 3.   a3_3 equals 22.  c3_3 equals 1.

2nd round: b2_3 equals 1.  a2_3 equals 10.  c3_3 equals -1.  The link stops here, because c3_3 equals -1. 

The link is (30, 22, 10)

The other links in the example:

(32, 20)

(31, 21, 11, 3)

(30, 22, 10)

(12, 1)

(2)

WANT: 

data want;
input d1_1-d1_10 d2_1-d2_10 d3_1-d3_10 d4_1-d4_10 d5_1 - d5_10;
datalines;
32 20 . . . . . . . . 31 21 11 3 . . . . . . 30 22 10 . . . . . . . 12 1 . . . . . . . . 2 . . . . . . . . . . 
run;

Let me know if you need further clarification.  Thank you. 

---

You would likely be much better off in the long run to have actual variables that hold information such as individual, round and score instead of what I see as a way too cumbersome wide structure. Also any solution that works for your current structure will need to be almost completely rewritten to handle 5 rounds, or more or fewer participants.

If you have an actual numeric value for the round then the "previous round" can be referenced with the same personal id and round number = 1 less.

What kind of questions are you going to be answering using this data set?

And perhaps you want Proc Iml.

[smend]

I'm trying to link all the scores made on the same account the user had created from 2013 to 2019.

[ballardw]

---

@smend wrote:

I'm trying to find out how many games this individuals had played and what this individual had scored in the previous round. 

From the example, you can see is that this individual had played 6 games total.  Scored: (32, 20), (31, 21, 11, 3), (30, 22, 10), (12, 1), and (2).  

Actually I can't obvious see that. First, you did not explain what -1 means.

Second I can't tell what is supposed to represent an individual or round. Is A B C supposed to indicate individual or round?

You really should explicitly state what each of the components of your variable names means as they are storing data in the variable name that is needed for processing.

So in

A1_1 what does the A represent, what does the 1,2 or 3 immediately after A represent and what does the number after the _ represent. I might guess the last is "game number" or similar, but that is guessing.

And if you want to sum, calculate differences, means or such then likely instead of -1 you should be using the SAS value MISSING.

[smend]

I updated the post.  Let me know if it clarifies what I am trying to look for.  

[KachiM]

As Ballardw said, make your problem simpler to understand. Make 3 variables instead of 10. Fill all cells. Show the output data set desired and then the rules(logic) of deriving the answer.

[smend]

I edited my original post.  please let me know if it further clarifies what I am searching for. 

[KachiM]

I have gone through your revised (with 3 cells) array specs. Carefully understood until I reached:

"I would like to create an array which will display all the scores made on each account for this individual (the most recent score to the older score).  In the example, the player created 5 accounts from 2013 to 2019 and the scores ...."

I am unable understand 'individual', and 'account'. Is this for one individual or for several? I don't know the game and hence I could not guess anymore. Please read carefully once again the suggestions made by Ballardw.