## Proc-SQL-MINID-equivalent

Solved
Super Contributor
Posts: 355

# Proc-SQL-MINID-equivalent

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

Accepted Solutions
Solution
‎11-15-2016 05:29 AM
Super Contributor
Posts: 319

## Re: Proc-SQL-MINID-equivalent

Posted in reply to user24feb

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

All Replies
Solution
‎11-15-2016 05:29 AM
Super Contributor
Posts: 319

## Re: Proc-SQL-MINID-equivalent

Posted in reply to user24feb

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;``````
Trusted Advisor
Posts: 1,848

## Re: Proc-SQL-MINID-equivalent

Posted in reply to user24feb

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

Super User
Posts: 10,850

## Re: Proc-SQL-MINID-equivalent

Posted in reply to user24feb
```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;

```
Super Contributor
Posts: 355

## Re: Proc-SQL-MINID-equivalent

.. a random one, since the solution is not unique.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
• 4 replies
• 359 views
• 2 likes
• 4 in conversation