Currently the dataset is at account level, the purpose is to transform it into customer level. I am wondering is there a way to achieve that using array/sql/proc transpose? Thanks in advance.
data have;
input cust_num acct_num bal limit flag $;
cards;
111 1234 1000 3000 Y
111 2345 1050 4000 Y
111 3456 1050 4000 Y
111 4567 1020 2000 Y
111 6789 1000 3000 Y
222 4561 2500 5000 N
222 4572 2600 4000 N
222 4583 3500 5000 N
333 5611 5000 9000 Y
333 5622 5500 8000 Y
333 5633 5100 7500 Y
333 5644 4800 8000 Y
;
run;
The final dataset:
cust_num | flag | acct_num1 | bal | limit | acct_num2 | bal | limit | acct_num3 | bal | limit | acct_num4 | bal | limit | acct_num5 | bal | limit |
111 | Y | 1234 | 1000 | 3000 | 2345 | 1050 | 4000 | 3456 | 1050 | 4000 | 4567 | 1020 | 2000 | 6789 | 1000 | 3000 |
222 | N | 4561 | 2500 | 5000 | 4572 | 2600 | 4000 | 4583 | 3500 | 5000 | ||||||
333 | Y | 5611 | 5000 | 9000 | 5622 | 5500 | 8000 | 5633 | 5100 | 7500 | 5644 | 4800 | 8000 |
The simplest way is IDGROUP, if you have big table try MERGE skill me,Arthur.T and Matt proposed .
http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf
data have;
input cust_num acct_num bal limit flag $;
cards;
111 1234 1000 3000 Y
111 2345 1050 4000 Y
111 3456 1050 4000 Y
111 4567 1020 2000 Y
111 6789 1000 3000 Y
222 4561 2500 5000 N
222 4572 2600 4000 N
222 4583 3500 5000 N
333 5611 5000 9000 Y
333 5622 5500 8000 Y
333 5633 5100 7500 Y
333 5644 4800 8000 Y
;
run;
proc sql noprint;
select max(n) into : n
from (select count(*) as n from have group by cust_num,flag );
quit;
proc summary data=have;
by cust_num flag;
output out=want idgroup(out[&n] ( acct_num bal limit)=);
run;
There are many posts here, and on the web showing various methods of reshaping data from long to wide and vice versa:
For example. Or good ol Lex Jansen:
http://www.lexjansen.com/nesug/nesug12/ff/ff01.pdf
The simplest way is IDGROUP, if you have big table try MERGE skill me,Arthur.T and Matt proposed .
http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf
data have;
input cust_num acct_num bal limit flag $;
cards;
111 1234 1000 3000 Y
111 2345 1050 4000 Y
111 3456 1050 4000 Y
111 4567 1020 2000 Y
111 6789 1000 3000 Y
222 4561 2500 5000 N
222 4572 2600 4000 N
222 4583 3500 5000 N
333 5611 5000 9000 Y
333 5622 5500 8000 Y
333 5633 5100 7500 Y
333 5644 4800 8000 Y
;
run;
proc sql noprint;
select max(n) into : n
from (select count(*) as n from have group by cust_num,flag );
quit;
proc summary data=have;
by cust_num flag;
output out=want idgroup(out[&n] ( acct_num bal limit)=);
run;
Sorry about the repeat and emty post. Just stated posting on this site.
%let pgm=utl_XpoMnyVar;
* I like to approach a problem like this using name/value pairs;
HAVE
Up to 40 obs WORK.HAVE total obs=12
Obs CUST_NUM ACCT_NUM BAL LIMIT FLAG
1 111 1234 1000 3000 Y
2 111 2345 1050 4000 Y
3 111 3456 1050 4000 Y
4 111 4567 1020 2000 Y
5 111 6789 1000 3000 Y
6 222 4561 2500 5000 N
7 222 4572 2600 4000 N
8 222 4583 3500 5000 N
9 333 5611 5000 9000 Y
10 333 5622 5500 8000 Y
11 333 5633 5100 7500 Y
12 333 5644 4800 8000 Y
WANT
Middle Observation(1 ) of Last dataset = WORK.HAVXPO - Total Obs 3
-- CHARACTER --
FLAG C 8 Y
-- NUMERIC --
CUST_NUM N 8 111
ACCT_NUM1 N 8 1234
BAL1 N 8 1000
LIMIT1 N 8 3000
ACCT_NUM2 N 8 2345
BAL2 N 8 1050
LIMIT2 N 8 4000
ACCT_NUM3 N 8 3456
BAL3 N 8 1050
LIMIT3 N 8 4000
ACCT_NUM4 N 8 4567
BAL4 N 8 1020
LIMIT4 N 8 2000
ACCT_NUM5 N 8 6789
BAL5 N 8 1000
LIMIT5 N 8 3000
SOLUTION
* Make 'HAVE' long and skinny;
* long and skinny is often a more useful structure;
* make fat as needed;
data have(drop=acct_num--limit lagcustnum);
retain rep 0;
input cust_num acct_num bal limit flag $;
lagcustnum=lag(cust_num);
if lagcustnum ne cust_num then rep=0;
rep=rep+1;
nam='ACCT_NUM';val=acct_num;output;
nam='BAL';val=bal;output;
nam='LIMIT';val=limit;output;
cards;
111 1234 1000 3000 Y
111 2345 1050 4000 Y
111 3456 1050 4000 Y
111 4567 1020 2000 Y
111 6789 1000 3000 Y
222 4561 2500 5000 N
222 4572 2600 4000 N
222 4583 3500 5000 N
333 5611 5000 9000 Y
333 5622 5500 8000 Y
333 5633 5100 7500 Y
333 5644 4800 8000 Y
;
run;
/*
Up to 40 obs WORK.HAVE total obs=36
Obs REP CUST_NUM FLAG NAM VAL
1 1 111 Y ACCT_NUM 1234
2 1 111 Y BAL 1000
3 1 111 Y LIMIT 3000
4 2 111 Y ACCT_NUM 2345
5 2 111 Y BAL 1050
6 2 111 Y LIMIT 4000
7 3 111 Y ACCT_NUM 3456
8 3 111 Y BAL 1050
9 3 111 Y LIMIT 4000
10 4 111 Y ACCT_NUM 4567
11 4 111 Y BAL 1020
12 4 111 Y LIMIT 2000
13 5 111 Y ACCT_NUM 6789
14 5 111 Y BAL 1000
15 5 111 Y LIMIT 3000
16 1 222 N ACCT_NUM 4561
17 1 222 N BAL 2500
18 1 222 N LIMIT 5000
19 2 222 N ACCT_NUM 4572
*/
* make fat as needed;
* trivial code, long and skinny lends itself to powerfull SQL processing;
proc transpose data=have out=havxpo ;
by cust_num flag;
id nam rep;
var val ;
run;quit;
Middle Observation(1 ) of Last dataset = WORK.HAVXPO - Total Obs 3
-- CHARACTER --
FLAG C 8 Y
-- NUMERIC --
CUST_NUM N 8 111
ACCT_NUM1 N 8 1234
BAL1 N 8 1000
LIMIT1 N 8 3000
ACCT_NUM2 N 8 2345
BAL2 N 8 1050
LIMIT2 N 8 4000
ACCT_NUM3 N 8 3456
BAL3 N 8 1050
LIMIT3 N 8 4000
ACCT_NUM4 N 8 4567
BAL4 N 8 1020
LIMIT4 N 8 2000
ACCT_NUM5 N 8 6789
BAL5 N 8 1000
LIMIT5 N 8 3000
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.