topic Re: Transform vertical data into horizontal data in SAS Programming

Transform vertical data into horizontal data

zhige50 — Tue, 14 Jun 2016 14:50:10 GMT

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

Re: Transform vertical data into horizontal data

RW9 — Tue, 14 Jun 2016 14:54:11 GMT

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/Nekhayevska-ReshapingData-Fall2012.pdf

For example. Or good ol Lex Jansen:

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

Re: Transform vertical data into horizontal data

Cynthia_sas — Tue, 14 Jun 2016 17:20:24 GMT

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

Re: Transform vertical data into horizontal data

Ksharp — Wed, 15 Jun 2016 06:33:28 GMT

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;

Re: Transform vertical data into horizontal data

rogerjdeangelis — Sat, 18 Jun 2016 23:58:23 GMT

Re: Transform vertical data into horizontal data

rogerjdeangelis — Sun, 19 Jun 2016 00:02:20 GMT

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