topic Re: Min function Help in SAS Programming

Min function Help

Kalai2008 — Wed, 25 Oct 2017 20:20:32 GMT

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;

Re: Min function Help

kiranv_ — Wed, 25 Oct 2017 20:32:10 GMT

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;

Re: Min function Help

Kalai2008 — Wed, 25 Oct 2017 20:45:57 GMT

Thank you It worked!