ColMin in SAS

[sasuser_sk]

Hi I use ColMin function in Brio which returns the smallest value in a column of numbers. EX: 

ColMin(Date,ID). Can someone help me find the same function in SAS or SAS EG. Thank you.

OUTPUT is:

ID Date ColMin
710 2/28/2021 2/28/2021
7111 2/28/2021 5/27/2020
7111 5/27/2020 5/27/2020
7119 2/28/2021 1/19/2021
7119 1/19/2021 1/19/2021
7121 2/26/2021 2/26/2021
7121 5/31/2021 2/26/2021

[Tom · Accepted solution]

What is the INPUT that produces that output?
You might want to use PROC SQL and the MIN() aggregate function.

proc sql;
create table want as
select id,date,min(date) as mindate format=mmddyy10.
 from have
 group by id
;
quit;

[sasuser_sk]

Thanks it worked!

[Tom]

Note that in general if you want to calculate statistics, like MIN, use one of the SAS procedures designed to do that.  PROC MEANS, PROC UNIVARIATE, PROC FREQ, etc.

[Reeza]

It depends on what you're doing in the next steps as to the best method to calculate aggregate statistics.

Here is an examples of how to add an average value to a data set, similar processes are available for other aggregate statistics.

******************************************************;
*Add average value to a dataset;
*Solution 1 - PROC MEANS + Data step;
******************************************************;

proc means data=sashelp.class noprint;
    output out=avg_values mean(height)=avg_height;
run;

data class_data;
    set sashelp.class;

    if _n_=1 then
        set avg_values;
run;

proc print data=class;
run;

*Solution 2 - PROC SQL - note the warning in the log;
PROC SQL;
Create table class_sql as
select *, mean(height) as avg_height
from sashelp.class;
quit;

******************************************************;
*Add average value to a dataset - with grouping variables;
*Solution 1 - PROC MEANS + Data step;
******************************************************;
proc means data=sashelp.class noprint nway;
class sex;
    output out=avg_values mean(height)=avg_height;
run;

*sort data before merge;
proc sort data=sashelp.class out=class;
by sex;
run;

data class_data;
 merge class avg_values;
 by sex;


run;

proc print data=class_data;
run;

*Solution 2 - PROC SQL - note the warning in the log;
PROC SQL;
Create table class_sql as
select *, mean(height) as avg_height
from sashelp.class
group by sex;
quit;

---

@sasuser_sk wrote:

Hi I use ColMin function in Brio which returns the smallest value in a column of numbers. EX: 

ColMin(Date,ID). Can someone help me find the same function in SAS or SAS EG. Thank you.

OUTPUT is:

ID Date ColMin
710 2/28/2021 2/28/2021
7111 2/28/2021 5/27/2020
7111 5/27/2020 5/27/2020
7119 2/28/2021 1/19/2021
7119 1/19/2021 1/19/2021
7121 2/26/2021 2/26/2021
7121 5/31/2021 2/26/2021

---

[sasuser_sk]

Thank you Reeza. I will be needing these steps when I proceed further in my project.