## Transform vertical data into horizontal data

Solved
Occasional Contributor
Posts: 12

# Transform vertical data into horizontal data

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

Accepted Solutions
Solution
‎06-15-2016 09:46 AM
Super User
Posts: 10,778

## Re: Transform vertical data into horizontal data

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;``````

All Replies
Super User
Posts: 9,599

## Re: Transform vertical data into horizontal data

There are many posts here, and on the web showing various methods of reshaping data from long to wide and vice versa:

http://www.sas.com/content/dam/SAS/en_ca/User%20Group%20Presentations/Edmonton-User-Group/Nekhayevsk...

For example.  Or good ol Lex Jansen:

http://www.lexjansen.com/nesug/nesug12/ff/ff01.pdf

SAS Super FREQ
Posts: 9,368

## Re: Transform vertical data into horizontal data

Hi, just FYI, you can only have 1 variable in the new dataset named "bal". like the acct_num variable, your "bal" variables will have to be named like:
acct_num1 goes with bal1
acct_num2 goes with bal2
etc.

cynthia
Solution
‎06-15-2016 09:46 AM
Super User
Posts: 10,778

## Re: Transform vertical data into horizontal data

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;``````
Valued Guide
Posts: 505

Valued Guide
Posts: 505

## Re: Transform vertical data into horizontal data

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

``````
🔒 This topic is solved and locked.