BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
zhige50
Obsidian | Level 7

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      
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

 

 

 

Cynthia_sas
SAS Super FREQ
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
Ksharp
Super User

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;
rogerjdeangelis
Barite | Level 11

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

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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