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?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.