How to "Transpose" data

[bbb_NG]

Dear all,

     I have a sasdata with the following format

CustomerNo    CustomerAccountType    Balance

001     Saving Account     100.00

001     Term Deposit    200.00

002    Saving Account     400.00

002     Term Deposit    600.00

     I want the format as follows:

CustomerNo    SavingAccountBalance     TermDepositBalance

001     100.00    200.00

002     400.00    600.00

I have succeded in achieving the latter format by using 2 queries one for saving one for TD then merge,but is there any easier way for it?

[ArtC · Accepted solution]

Take a look at proc transpose.  The following should get you close.

data have;

input CustomerNo  @5 CustomerAccountType  $14.  Balance;

datalines;

001 Saving Account  100.00

001 Term Deposit    200.00

002 Saving Account  400.00

002 Term Deposit    600.00

run;

proc transpose data=have

               out=want;

by customerno;

id customeraccounttype;

var balance;

run;

proc print data=want;

run;

[bbb_NG]

Dear ArtC,

     Thanks for your prompt response.I didn't expect to have reply so soon.

     Due to my green hand status quo, can you suggest where to know the exact explanation/usage of these functions, say transpose?

Thanks again

Dawn

[ArtC]

Google SAS+Transpose 

There have been a number of papers done on the TRANSPOSE procedure.  Linlin's DATA step array method can ultimately be more flexible, but not necessarily easier for the new user.

[Linlin]

another approach and borrowed ArtC's data:

data have;

input CustomerNo  @5 CustomerAccountType  $14.  Balance;

datalines;

001 Saving Account  100.00

001 Term Deposit    200.00

002 Saving Account  400.00

002 Term Deposit    600.00

run;

data want;

  do _n_=1 by 1 until(last.CustomerNo);

    set have;

     by CustomerNo;

     array b(*) SavingAccountbalance TermDepositbalance;

     b(_n_)=balance;

     end;

     drop balance CustomerAccountType;

run;

proc print;run;

                             Customer        Saving             Term

                       Obs       No       Accountbalance    Depositbalance

                        1         1             100               200

                        2         2             400               600

Linlin

[data_null__]

Your data step for transposing to wide format is fraught with peril. :smileyshocked:  Your assumption that the index (_N_) into the array may not be valid, at the very least you need to figure a way to index into the array based on the value of CustomerAccountType using an INFORMAT perhaps or an if you want to get fancy an associative array that maps CustomerAccountType to array index.

This is where PROC TRANSPOSE really “shines”, creating meta data from data. 

[Linlin]

Hi Data _null_,

Thank you for your comments. I used the techniques learned from this paper:

http://support.sas.com/resources/papers/proceedings09/038-2009.pdf

Linlin

[ArtC]

For those browsing this thread, the paper cited by Linlin is very good, but it also discusses a number of advanced techniques.  If you use this paper and its suggestions be sure that you truly understand the concepts.  If you want to get an advanced in depth understanding of the DO loop, this is a very good place to start.  But as Data _Null_ said 'be careful'.  There are dangers for those who only partially understand the techniques and suggestions.

[bbb_NG]

Linlin &Data_null_,

     Thank u both for taking time to solve my problem. I'm very appreciated.Thanks