DATA Step, Macro, Functions and more

Min function Help

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 91
Accepted Solution

Min function Help

 Given Data: 

 

Id             Name               Amount
1            Blue                   $30
1               red                  $15
1            Green                  $23
1           Orange                $10

2...

 

I want to create a new column where I need only min value name orange, as the min total amount is $10.

Max function not working for new column as it is sorting in alphabetical order(Character variable). Any way to do it in single data step without joining ? 

I tried with case statement too.

 

Id            Name             Amount      Newcol
1           Blue                 $30             Orange
1            red                  $15             Orange
1           Green               $23            Orange
1           Orange             $10              Orange

 

proc sql;
create table want as
select id, Name,min(Amount) as Min_Tot,
max(Amountt) as Max_Tot, min(name) as Min_name
from table have;
run;


Accepted Solutions
Solution
‎10-25-2017 04:46 PM
PROC Star
Posts: 500

Re: Min function Help

Posted in reply to Kalai2008

something like this

proc sql;
create table want as
select a.*,b.new_col from 
(select * from have)a
inner join
(select id, name as new_col from have
group by ID
having amount =min(amount))b
on a.id =b.id;

View solution in original post


All Replies
Solution
‎10-25-2017 04:46 PM
PROC Star
Posts: 500

Re: Min function Help

Posted in reply to Kalai2008

something like this

proc sql;
create table want as
select a.*,b.new_col from 
(select * from have)a
inner join
(select id, name as new_col from have
group by ID
having amount =min(amount))b
on a.id =b.id;
Frequent Contributor
Posts: 91

Re: Min function Help

Thank you It worked!
☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 125 views
  • 1 like
  • 2 in conversation