topic Re: Select minimums through different columns in SAS Programming

Select minimums through different columns

podarum — Wed, 07 Mar 2018 15:45:47 GMT

I have a bunch of accounts and they are each have 12 months of data (stacked by row)... 3 separate columns that have dollar figures and I'm trying to get the minimum dollars from those columns all in one row.

HAVE

Acct Date Col1 Col2 Col3 Bal

001 Jan17 0 0 0 40

001 Feb17 23 0 0 23

001 Mar17 23 23 0 23

001 Apr17 20 20 20 20

001 May17 12 12 0 12

002 Dec16 0 0 0 34

002 Jan17 0 0 0 30

002 Feb17 0 0 0 15

And so on similar for many accounts.

WANT

Acct col1 Col2 Col3 Bal1 Bal2 Bal3

001 12 20 20 12 20 20

002 0 0 0 15 15 15

Re: Select minimums through different columns

Reeza — Wed, 07 Mar 2018 15:58:49 GMT

PROC MEANS/SQL/SUMMARY/UNIVARIATE are all options.

Have you tried any of those so far? If so, and they don't work please post your code.

@podarum wrote:

I have a bunch of accounts and they are each have 12 months of data (stacked by row)... 3 separate columns that have dollar figures and I'm trying to get the minimum dollars from those columns all in one row.

HAVE

Acct    Date     Col1   Col2    Col3    Bal

001    Jan17     0          0        0        40

001    Feb17    23        0        0         23

001    Mar17    23       23       0         23

001    Apr17    20       20       20       20

001   May17    12       12       0        12

002   Dec16     0         0        0         34

002   Jan17      0         0        0        30

002   Feb17     0         0         0       15

And so on similar for many accounts.

WANT

Acct   col1    Col2    Col3     Bal1       Bal2      Bal3

001    12        20        20        12         20          20

002     0          0          0         15         15          15

Re: Select minimums through different columns

RW9 — Wed, 07 Mar 2018 15:59:04 GMT

Post test data in the form of a datastep - not here to type in test data!

Maybe (not tested):

data want;
  set have;
  array col{3};
  array _col{3} 8.;
  do i=1 to 3;
    if col{i} ne . and (col{i} < _col{i} or _col{i}=.) then _col{i}=col{i};
  end;
run;

Note, I have no idea what bal1-3 are supposed to represent. Also, why is col1 not 0 in the first instance?

Re: Select minimums through different columns

podarum — Wed, 07 Mar 2018 16:00:52 GMT

I've tried proc sql ;

Re: Select minimums through different columns

Reeza — Wed, 07 Mar 2018 16:03:13 GMT

I'm assuming you want what you said which is:

I'm trying to get the minimum dollars from those columns all in one row.

However your example looks like you want the minimum NON ZERO values, which is a very different problem.

Which one are you trying to accomplish?

@podarum wrote:

I've tried proc sql ;

Please post the code from this.