M. Moore M. Moore - 1 year ago 139
MySQL Question

Primary Key using SAS

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

I created two tables with the same data, then used the following

Code used:

proc sql;
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;

Answer Source

This probably isn't the best solution but I recommend a two step process.

  1. Find the max for each ID
  2. Assign Primary key. Use a flag variable to indicate if the max_rate is the first occurrence.

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;

    *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;

    proc print data=want;

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;

 data want;
 set have;
 by id;
 if first.id then key='Primary';

 proc sort data=want;
 by id line;

proc print data=want;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download