Proc-SQL-MINID-equivalent

[user24feb]

Hello,

I am looking for an SQL-equivalent to this:

Data A;
  Infile Datalines DLM='09'x;
  Format ID $15.;
  Input ID $ Costs ;
  ID_short=Scan(ID,1,'_');
  Datalines;
460_01	45
460_02	50
460_03	60
461_01	99
461_02	98
461_03	100
463_01	45
463_02_469_01	40
463_03_469_01	40
463_04_469_01	40
463_05	55
;
Run;

Proc Means Data=A NoPrint;
  By ID_Short;
  Var Costs;
  Output Out=B (Drop=_:) MinID(Costs(ID))=ID Min=;
Run;

.. the problem ist of course, that there are multiple results for ID_short='463'.

Thx

[Loko · Accepted solution]

Hello,

proc sql;
create table want as
select t1.ID_Short, t1.minimum as costs, min(t2.id) as id from
(
(
select ID_Short,min(Costs) as minimum
from a
group by ID_Short
) as t1
left join
(
select *
from a
) as t2
on t1.ID_Short=t2.ID_Short and t1.minimum=t2.Costs
)
group by t1.ID_Short, t1.minimum
;
quit;

[Shmuel]

May be you meant to do:

Proc Means Data=A NoPrint;
  By ID_Short;
  Var Costs;
  Output Out=B (Drop=_:) IDGROUP(min(costs) out[3] (costs)=Min_costs);
Run;

[Ksharp]

Which one you would retain if there are multiple obs for '463'.

Data A;
  Infile Datalines expandtabs;
  Format ID $15.;
  Input ID $ Costs ;
  ID_short=Scan(ID,1,'_');
  Datalines;
460_01	45
460_02	50
460_03	60
461_01	99
461_02	98
461_03	100
463_01	45
463_02_469_01	40
463_03_469_01	40
463_04_469_01	40
463_05	55
;
Run;

proc sql;
create table want as
select * from (
 select *
  from a
   group by id_short
    having costs=min(costs)
)
group by id_short,costs
 having id=min(id);
quit;

[user24feb]

.. a random one, since the solution is not unique.