topic Re: ColMin in SAS in SAS Programming

ColMin in SAS

sasuser_sk — Wed, 21 Jul 2021 21:21:21 GMT

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

Re: ColMin in SAS

Tom — Wed, 21 Jul 2021 21:28:45 GMT

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;

Re: ColMin in SAS

Reeza — Wed, 21 Jul 2021 21:42:46 GMT

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

Re: ColMin in SAS

sasuser_sk — Wed, 21 Jul 2021 21:44:30 GMT

Thanks it worked!

Re: ColMin in SAS

sasuser_sk — Wed, 21 Jul 2021 21:46:03 GMT

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

Re: ColMin in SAS

Tom — Wed, 21 Jul 2021 21:47:45 GMT

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.