Transform vertical data into horizontal data

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

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: 9,874

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,720

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: 8,820

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: 9,874

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

Re: Transform vertical data into horizontal data

 
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 396 views
  • 0 likes
  • 5 in conversation