CODEWITHSUNDEEP
sasenterprise-guide
Gilga
Gilga

Reputation: 1

SAS Second smallest value

The following code, built using the Summary Statistics task from SAS Enterprise Guide, finds the min of each column of a table.

How can I find the second smallest value? I tried replacing MIN with SMALLEST(2) but doesn't work.

Thank you.

TITLE;
TITLE1 "Summary Statistics";
TITLE2 "Results";
FOOTNOTE;
FOOTNOTE1 "Generated by the SAS System (&_SASSERVERNAME, &SYSSCPL) on 
%TRIM  (%QSYSFUNC(DATE(), NLDATE20.)) at 
%TRIM(%SYSFUNC(TIME(), TIMEAMPM12.))";
PROC MEANS DATA=WORK.SORTTempTableSorted
NOPRINT
CHARTYPE

    MIN NONOBS  ;
VAR A B C;

OUTPUT  OUT=WORK.MEANSummaryStats(LABEL="Summary Statistics for
WORK.QUERY_FOR_TRNSTRANSPOSEDPD__0001")

    MIN()=

/ AUTONAME AUTOLABEL INHERIT
;

RUN;

Upvotes: 0

Views: 1754

Answers (5)

data _null_
data _null_

Reputation: 9109

Using the ExtremeValue table from PROC UNIVARIATE.

ods select none;
ods output ExtremeValues=ExtremeValues(where=(loworder=2) drop=high:);
proc univariate data=sashelp.class NEXTRVAL=2;
   run;
ods select all;
proc print;
   run;

enter image description here

Upvotes: 1

Victor
Victor

Reputation: 17097

Sort the values in ascending order. Delete the first value. This would be the minimum value. Now the value left at the first position is your second minimum.

Upvotes: 0

user667489
user667489

Reputation: 9569

A data step solution that should work for any number of columns without ever running into macro limitations:

proc sql noprint;
  select count(*) into :NUM_COUNT from dictionary.columns
    where LIBNAME='SASHELP' and MEMNAME = 'CLASS' and TYPE = 'num';
quit;

data class_min2;
  do until(eof);
    set sashelp.class end = eof;
    array min2[&NUM_COUNT,2] _temporary_;
    array nums[*] _numeric_;
    do _n_ = 1 to &NUM_COUNT;
      min2[_n_,1] = min(min2[_n_,1],nums[_n_]);
      if min2[_n_,1] < nums[_n_] then min2[_n_,2] = min(nums[_n_],min2[_n_,2]); 
    end;
  end;
  do _iorc_ = 1 to 2;
    do _n_ = 1 to &NUM_COUNT;
      nums[_n_] = min2[_n_,_iorc_];
    end;
    output;
  end;
  keep _NUMERIC_;
run;

This outputs the two lowest distinct values of each numeric variable, without transposing the data in the same way that proc univariate does. You can easily allow for duplicate minimum values with a bit of tweaking.

Upvotes: 0

Ludwig61
Ludwig61

Reputation: 141

I will assume you are interested in all numeric columns. If the IFLIST macro variable is longer than 64k bytes in length due to the number of numeric variables and the length of their names, this code will fail. It should work for all reasonably narrow data sets.

UNTESTED CODE

We get a list of the variables in your data set.

PROC CONTENTS DATA=SORTTempTableSorted OUT=md NOPRINT ;
RUN ;

We use that list to create statements and expressions.

IFLIST is a block of statements to store the minimum value of fieldname in fieldname_1 and the second lowest in fieldname_2. If the comparison is LT, then we keep distinct values, not necessarily the order statistics. If the comparision is LE and there are multiple observations with the minimum value, fieldname_1 and fieldname_2 will be equal to each other. I will assume you want distinct values.

MAXLIST is an expression that will resolve to the largest numeric value in the data set :)

MINLIST and MINLIST2 are created for use in RETAIN and KEEP statements.

PROC SQL STIMER NOPRINT EXEC ;
  SELECT 'IF ' || name || ' LT ' || name '_1 THEN DO;' || 
                  name || '_2=' || name || '_1;' || 
                  name || '_1=' || name || ';END;ELSE IF ' || 
                  name || ' LT ' || name || '_2 THEN ' || 
                  name || '_2=' || name,
         'MAX(' || name || ')',
         name || '_1',
         name || '_2'
    INTO :iflist SEPARATED BY '; ',
         :maxlist SEPARATED BY '<>'
         :minlist SEPARATED BY ' ',
         :min2list SEPARATED BY ' '
    FROM md
    WHERE type EQ 1
  ;

Now we get the largest numeric value from the data set:

  SELECT &maxlist
    INTO :maxval
    FROM SORTTempTableSorted
  ;

QUIT ;

Now we do the work. The END option sets "eof" to 1 on the last observation, which is the only time we want to write a record to the output data set.

DATA min2 ;
  SET SORTTempTableSorted END=eof;
  RETAIN &minlist &min2list &maxval;
  KEEP &minlist &min2list ;
  &iflist ;
  IF eof THEN
    OUTPUT ;
RUN ;

Upvotes: 0

Reeza
Reeza

Reputation: 21274

I don't think there's any way to accomplish this within proc means. There are ways using a variety of other procs. The Univariate procedure highlights one method using the Extreme Observations.

https://support.sas.com/documentation/cdl/en/procstat/63104/HTML/default/viewer.htm#procstat_univariate_sect058.htm

title 'Extreme Blood Pressure Observations';
ods select ExtremeObs;
proc univariate data=BPressure;
   var Systolic Diastolic;
   id PatientID;
run;

proc print data=ExtremeObs;
run;

Upvotes: 0

Related Questions