DATA Step, Macro, Functions and more

Proc-SQL-MINID-equivalent

Accepted Solution Solved
Reply
Super Contributor
Posts: 340
Accepted Solution

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: 308

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;

View solution in original post


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

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,554

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,020

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: 340

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
  • 257 views
  • 2 likes
  • 4 in conversation