BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
user24feb
Barite | Level 11

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

1 ACCEPTED SOLUTION

Accepted Solutions
Loko
Barite | Level 11

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

4 REPLIES 4
Loko
Barite | Level 11

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
Garnet | Level 18

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
Super User
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
Barite | Level 11
.. a random one, since the solution is not unique.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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