Hi I need help translating the below table in SAS code to be able to assign the values found in the variable "Points" in another large target dataset. The target dataset contains the variables "Sport", "Tournament", "Held", "Age", "Country", "Season", "Year", "Length", and "Surface" except the variable "Points". So the table below can be seen as a map that has the assignment rules branching like tree to the "Point" value. Some observations about the map table (below) and desired target dataset: The variables "Sport" and "Tournament" will always have values populated and the other variables can have blank values. The "Points" can be assigned in a decision tree format branching from first column "Sport" then down to the last column "Surface". Hence the order of the columns is very important. The "Points" assignment is will be based on the criteria shown in each row. If perfect match is not found the row with a most closely match will be used to assign the "Points" value Blank value can take any value not explicitly specified in the map table, e.g., see row #3... if in the target dataset a record matches all values (i.e., tennis, 34, Y,(15-30), France, Fall,2017, 5 Days) except the Surface variable which has a value "Grass" then this record will still get the Points assignment of 39 My initial idea was to create a conditional statement for each row and assign the "Points" value that way. For instance if we take the example above for row #3 I would have something like if (Sport='Tennis'
and Tournament=45
and Held='Y'
and Age='(15-30)'
and Country='France'
and Season='Fall'
and Year=2017
and Length ='5 Days'
and Surface ne 'Hard'
and Surface ne 'Clay'
) then Points=39; The above seems pretty time consuming especially if the map table has 100+ conditional rows. I was wondering if there is a better way to tackle this problem. Dataset A: # Sport Tournament Held Age Country Season Year Length Surface Points 1 Tennis 45 Y (15-30) Italy Fall 2017 5 Days Hard 44 2 Tennis 45 Y (15-30) France Summer 2017 5 Days Clay 46 3 Tennis 45 Y (15-30) France Fall 2017 5 Days 39 4 Tennis 45 Y (15-30) Austria Summer 2016 5 Days 44 5 Tennis 45 Y (30-60) Italy Summer 2014 44 6 Tennis 45 Y (30-60) France Summer 2017 46 7 Tennis 45 Y (15-30) Italy Summer 2014 45 8 Tennis 45 Y (15-30) France Summer 2017 46 9 Tennis 45 Y (15-30) Austria Summer 2014 44 10 Tennis 45 Y (30-60) France Summer 44 11 Tennis 45 Y (15-30) France Summer 44 12 Tennis 38 N (5-15) France Spring 26 13 Tennis 38 N (5-15) Austria Summer 27 14 Tennis 38 N (30-60) Italy Winter 26 15 Tennis 38 N (30-60) Italy Spring 26 16 Tennis 38 N (15-30) Austria Spring 26 17 Tennis 38 N (5-15) Austria 26 18 Tennis 38 Y 30 19 Tennis 54 N 9 20 Tennis 54 21 21 Tennis 60 27 22 Golf 20 Y (5-15) Austria Summer 2017 3 Day Grass 18 23 Golf 20 Y (30-60) Italy Summer 2016 3 Day 18 24 Golf 20 Y (30-60) France Summer 2014 18 25 Golf 20 Y (15-30) Italy Summer 18 26 Golf 20 Y (15-30) Austria Summer 18 27 Golf 20 N 1 28 Golf 20 61 ... ... ... ... ... ... ... ... ... ... ... The target dataset with assigned values based on the rules provided in the table above should look like this: Dataset B: Sport Tournament Held Age Country Season Year Length Surface Points Tennis 45 Y (15-30) Italy Fall 2017 5 Days Hard 44 Tennis 45 Y (15-30) France Summer 2017 5 Days Clay 46 Tennis 45 Y (15-30) France Fall 2017 5 Days Grass 44 Tennis 45 Y (15-30) Austria Summer 2016 5 Days Green 44 Tennis 45 Y (30-60) Italy Summer 2014 7 Days Hard/Clay 44 Tennis 45 Y (30-60) France Summer 2017 10 Days Hard/Clay 46 Tennis 45 Y (15-30) Italy Summer 2014 7 Days Grass 44 Tennis 45 Y (15-30) France Summer 2017 7 Days Grass 46 Tennis 45 Y (15-30) Austria Summer 2014 7 Days Grass 44 Tennis 45 Y (30-60) France Summer 2018 5 Days Clay 44 Tennis 45 Y (15-30) France Summer 2018 5 Days Clay 44 Tennis 38 N (5-15) France Spring 2017 5 Days Clay 26 Tennis 38 N (5-15) Austria Summer 2017 5 Days Hard 27 Tennis 38 N (30-60) Italy Spring 2017 5 Days Hard 26 Tennis 38 N (15-30) Austria Spring 2017 7 Days Grass 26 Tennis 38 N (5-15) Austria Winter 2017 7 Days Grass 26 Tennis 38 Y (15-30) France Summer 2017 5 Days Clay 30 Tennis 54 N (15-30) Italy Fall 2017 5 Days Hard 9 Tennis 54 Y (15-30) Italy Summer 2014 7 Days Grass 21 Tennis 60 Y (15-30) France Summer 2018 5 Days Clay 27 Thank you for your help.
... View more