I have two questions. First being how do I create a function in SAS/SQL that takes in three numbers and outputs a number to be stored in a variable. See question below, and then chart for clarification.
Second question, how do I apply the aforementioned function on the current observation variable (in a datastep) onto every other value in a column WITHIN a by group.
Please see the "chart" below for a clearer picture.
This is (approximately) what my dataset looks like.
RaceNum PlayerID Rank OldScore NewScore
Race1 25909 1 90 . -> avg(F(90, 87, 1), F(90, 83, 1), ..., F(90, 66, 1))
Race1 34391 2 87 . -> avg(F(87, 90, 0), F(87, 83, 1), ..., F(87, 66, 1))
Race1 38971 3 83 . -> avg(F(83, 90, 0), F(83, 87, 0), ..., F(83, 66, 1))
... ... ... ... . -> ...
Race1 23023 8 66 .-> avg(F(66, 90, 0), F(66, 87, 0), ..., F(66, 73, 0))
Race2 44919 1 91 . -> ...
... ... ... ... . -> ...
Race 2 25921 16 77 . -> ...
... ... ... ... . -> ...
Race1276 35901 30 53 .
%F(athlete, opponent, winner) = *Where F is some user defined function. winner= 1 if opponent loses and otherwise = 1;
I was thinking of somehow transposing the dataset to get something like this:
RaceNum PlayerID Rank OldScore NewScore Rank1Score Rank2Score Rank3Score ... Rank8Score R16Sc
Race1 25909 1 90 . 90 87 83 66
Race1 34391 2 87 . 90 87 83 66
... ... .. ..
Race2 44919 1 91 91 ... ... 53
...
I could do this by possibly by having 50 if statements (largest number of racers in a race)
data test1; set test;
if Rank=1 then Rank1Score = OldScore; else if Rank=1 then Rank2Score = Oldscore; .... else if Rank=50 then Rank50Score = OldScore; run;
And then use SQL:
proc sql; create table test2 as select *, max(Rank1Score) as rnk1score, ...., max(Rank50Score) as rnk50score from test1 group by RaceNum; quit;
Or using retain and lag statements and then left joining the last entry in each race using SQL.
But even then, I am not sure how to go about Rank&NScore Columns in SAS...
tl;dr: I am stuck.
I appreciate any questions, ideas, criticisms, pointers, or even solutions ;).
Thank you.
Are you required to use SQL?
And what are you trying to do in plain language?
Would be a good idea if you clarified your question. Post some simple test data - in the form of a datastep - and what the output should look like. From that mass of text I gather than you are transposing your data up - unless its for an output file I would recommend not doing that as it will just make your coding more difficult.
Anyways, you can simply achieve what you want by:
/* assumes sorted! */ proc transpose data=have out=want prefix=rankscore; by racenum playerid; var oldscore; id rank; idlabel rank; run;
Note that this will make your variables rankscoreX, not as you suggested as it is far easier to work with variable names with the numeric suffix.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.