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
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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.