I would like to have PAUL (played multiple teams) target or any player target that have played for multiple teams to be put in the last team he played for to avoid duplicate target.
Anyone can help? Thanks.
DATA TEST;
infile DATALINES DLM=',' DSD;
INPUT SEASON PLAYER $ DIV $ TEAM $ TARGET;
DATALINES;
202101, JOHN,003,001,10
202102, JOHN,003,001,15
202103, JOHN,003,001,16
202101, PAUL,003,002,20
202101, PAUL,003,003,20
202101, PAUL,003,004,20
202102, PAUL,003,002,25
202102, PAUL,003,003,25
202102, PAUL,003,004,25
202103, PAUL,003,002,35
202103, PAUL,003,003,35
202103, PAUL,003,004,35
202101, KENT,,005,42
202102, KENT,,005,45
202103, KENT,,005,46
;
RUN;
My desired output will be:
Season Player Div Team Target
202101 JOHN 003 001 10
202102 JOHN 003 001 15
202103 JOHN 003 001 16
202101 PAUL 003 002 0
202101 PAUL 003 003 0
202101 PAUL 003 004 20
202102 PAUL 003 002 0
202102 PAUL 003 003 0
202102 PAUL 003 004 25
202103 PAUL 003 002 0
202103 PAUL 003 003 0
202103 PAUL 003 004 35
202101 KENT 005 42
202102 KENT 005 45
202103 KENT 005 46
@scb It's not clear to me what determines the "last team he played for" for example, Paul appears to have played in teams 002, 003 & 004 in the last season 202103.
The following code does provide what you asked for in your output, but I'm not sure the code will provide what you want. For example if you add the input record 202104, PAUL, 003,002,10 then what would the output look like:
DATA TEST;
infile DATALINES DLM=',' DSD;
INPUT SEASON PLAYER $ DIV $ TEAM $ TARGET;
DATALINES;
202101, JOHN,003,001,10
202102, JOHN,003,001,15
202103, JOHN,003,001,16
202101, PAUL,003,002,20
202101, PAUL,003,003,20
202101, PAUL,003,004,20
202102, PAUL,003,002,25
202102, PAUL,003,003,25
202102, PAUL,003,004,25
202103, PAUL,003,002,35
202103, PAUL,003,003,35
202103, PAUL,003,004,35
202101, KENT,,005,42
202102, KENT,,005,45
202103, KENT,,005,46
;
RUN;
/*202104, PAUL,003,002,10*/
/* Sort data into an order that will work */
proc sort data=test out=srtdTest ;
by player descending season descending team ;
run ;
data want ;
set srtdTest ;
by player descending season descending team ;
/* If it's not the first occurance of season then set target to 0 */
if not(first.season) then do ;
target=0 ;
end ;
run ;
proc sort data=want out=srtdWant ;
by player season team ;
run ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: