topic One-to-Many merge - how to limit the many back to one by an expression? in SAS Procedures

One-to-Many merge - how to limit the many back to one by an expression?

StephenLaRoche — Sat, 11 Oct 2014 17:07:53 GMT

I have simple one-to-many merge code like this...

data baseball.playershighestsalary;

merge baseball.players (in=InMaster) baseball.salaries (in=InSalaries);

by playerID;

if InMaster and InSalaries;

keep playerID birthYear birthMonth birthCountry salary;

run;

Salaries dataset contains a row for each player for each year, so there are multiple rows for most players.

I simply want my merged dataset to include one row per player, including the max salary that player ever received (from the Salaries dataset).

How to do it?

I'm a newbie working on a final project in a SAS programming class, so I appreciate any solution or quick tip. First time trying to get help on this community!

Thanks,

Steve

Re: One-to-Many merge - how to limit the many back to one by an expression?

Reeza — Sat, 11 Oct 2014 17:49:29 GMT

1. Limit dataset before merge to include the max salary or only one record per player

2. I don't think a data step merge can handle a many2many merge so you may want to use SQL

Re: One-to-Many merge - how to limit the many back to one by an expression?

Patrick — Sat, 11 Oct 2014 22:23:07 GMT

Have your Salaries data set sorted by "playerID Salary".

Amend your IF-condition to: if inMaster and inSalaries and last.playerID;

I wouldn't use "and inSalaries" in your condition as this means you would remove players from your result for which there is no record in "Salaries".

Re: One-to-Many merge - how to limit the many back to one by an expression?

stat_sas — Sat, 11 Oct 2014 23:48:26 GMT

proc sql;

create table baseball.playershighestsalary as

select a.playerid,birthYear, birthMonth, birthCountry, b.salary

from baseball.players a left join (select playerid,max(salary) as salary from baseball.salaries group by playerid) b

on a.playerid=b.playerid;

quit;

Re: One-to-Many merge - how to limit the many back to one by an expression?

Ksharp — Sun, 12 Oct 2014 12:42:30 GMT

If there was a tie for max salary ?

data players;
input id ;
cards;
1 
2
3
;
run;
data salaries;
input id salaries;
cards;
1 23
1 43
2 43
2 21
3 45
4 34
;
run;
proc sort data=players;by id;run;
proc sort data=salaries;by id descending salaries;run;
data playershighestsalary;
  InMaster=0;
  merge players (in=InMaster) salaries ;
  by id;
  if InMaster ;
run;

Xia Keshan