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
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
I've tried proc sql ;
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.
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?
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.