DATA Step, Macro, Functions and more

Select minimums through different columns

Reply
Super Contributor
Posts: 409

Select minimums through different columns

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

 

Super User
Posts: 24,026

Re: Select minimums through different columns

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

 


 

Super Contributor
Posts: 409

Re: Select minimums through different columns

I've tried proc sql ; 

Super User
Posts: 24,026

Re: Select minimums through different columns

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.

Super User
Super User
Posts: 9,855

Re: Select minimums through different columns

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?

Ask a Question
Discussion stats
  • 4 replies
  • 92 views
  • 0 likes
  • 3 in conversation