I would like a SAS program to find the primary service from a data set based on the line with the highest rate but when there is a tie make the first line primary. See data set below.
ID line rate outcome
TTT 1 .95 Primary
TTT 2 .43
RRR 1 .75 Primary
RRR 2 .75
AAA 1 .23
AAA 2 .12
AAA 3 .65 Primary
create table test as
select a.ID, a.line, a.rate
when ((a.ID = b.ID) and (a.rate ge b.rate)) then "Primary"
else ' '
end) as outcome
from table1 a,table2 b
where a.ID = b.ID;
This probably isn't the best solution but I recommend a two step process.
Here's an untested sample code:
*Calculate max rate per ID; proc sql; create table temp1 as select a.*, max(rate) as max_rate from table1 group by ID order by ID, line; quit; *Assign primary key; data want; set temp1; by ID; retain flag 0; if first.ID then flag=0; if rate=max_rate and flag=0 then do; flag=1; key='Primary'; end; run; proc print data=want; run;
Another option is a data step with sort, sort so you have the maximum with the minimum line at the top and use BY processing to set the key to Primary.
proc sort data=have; by ID descending rate line; run; data want; set have; by id; if first.id then key='Primary'; run; proc sort data=want; by id line; run; proc print data=want; run;