edit: Okay, I know what is missing. I still need to compare all of this to the other list I have, as mentioned in original post.
Are you saying that you have some "players" in your data who don't have any games that they played in?
yea, the original method I responded to did capture those (as in, if they didnt play,. no games would show up). Basically, I have a lineup, 10 players from each team in each game. I have a list of players, separately, that I want to see during the year. I had previously found out which games each player is in (some are in up to 100 games, some in none, some in 1 or 2.) Now, I just need to repeat that process but while adding the positions. I think it is messing up in terms of formatting and being able to read certain things.
If you can't solve it I suggest posting this as a new thread, supplying example data and the desired solution.
I think I figured out the main problem
Here is the data:
game_id | player_1_id | player_1_pos | player_2_id | player_2_pos | player_3_id | player_3_pos | player_4_id | player_4_pos | player_5_id | player_5_pos | player_6_id | player_6_pos | player_7_id | player_7_pos | player_8_id | player_8_pos | player_9_id | player_9_pos | player_10_id | player_10_pos |
1 | 279577 | SS | 136860 | RF | 407812 | LF | 204020 | 1B | 501896 | 3B | 425877 | C | 445055 | CF | 518614 | 2B | 346798 | P | 0 |
and so on.
Then I have a list:
player
2700
567432
123573
etc.
I want the following:
player games/positions
2700 1a 5b
so player 2700 appered in game 1 position a and then in game 5, position b. I have gotten to:
player games
2700 1, 2, 5 ,6
telling me player 2700 is in these games. But adding the positions seems more tricky.
So why not just merge that file with the final results? e.g.:
data players;
input player;
cards;
5
7
10
;
proc sort data=players;
by player;
run;
data h1;
input game
player1 pos1 $
player2 pos2 $
player3 pos3 $
player4 pos4 $
player5 pos5 $
player6 pos6 $
player7 pos7 $
player8 pos8 $
player9 pos9 $
player10 pos10 $;
cards;
1 1 a 5 b 6 c 8 d 7 e 10 f 12 g 14 h 16 i 18 j
2 1 a 2 b 3 c 4 d 5 e 6 f 7 g 8 h 9 i 10 k
3 1 a 2 b 5 c 7 d 9 e 11 f 13 g 15 h 17 i 19 k
;
data need (keep=game player pos);
set h1;
array players(*) player1-player10;
array position(*) $ pos1-pos10;
do i=1 to 10;
player=players(i);
pos=position(i);
output;
end;
run;
proc sort data=need;
by player game;
run;
data want (keep=player games);
set need;
length games $70;
retain games;
by player;
game_and_pos=catx(" ",game,pos);
if first.player then games=game_and_pos;
else games=catx(",",games,game_and_pos);
if last.player then output;
run;
data want;
merge want players (in=inb);
by player;
if inb;
run;
I am also not surprised finding these great papers on lookup:
http://www2.sas.com/proceedings/sugi22/CODERS/PAPER88.PDFhttp://www.ssc.wisc.edu/sscc/pubs/4-1.pdfhttp://www2.sas.com/proceedings/sugi22/CODERS/PAPER88.PDF
http://www2.sas.com/proceedings/sugi27/p011-27.pdf
http://analytics.ncsu.edu/sesug/2008/CC-024.pdf
Haikuo
I appreciate all your help. Looks like it works well
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.