DATA Step, Macro, Functions and more

How to "Transpose" data

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 99
Accepted Solution

How to "Transpose" data

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?


Accepted Solutions
Solution
‎02-09-2012 01:42 AM
Valued Guide
Posts: 634

How to "Transpose" data

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;

View solution in original post


All Replies
Solution
‎02-09-2012 01:42 AM
Valued Guide
Posts: 634

How to "Transpose" data

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;

Frequent Contributor
Posts: 99

How to "Transpose" data

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

Valued Guide
Posts: 634

Re: How to "Transpose" data

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.

Super Contributor
Posts: 1,636

Re: How to "Transpose" data

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

Respected Advisor
Posts: 3,799

How to "Transpose" data

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. 

Super Contributor
Posts: 1,636

How to "Transpose" data

Posted in reply to data_null__

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

Valued Guide
Posts: 634

Re: How to "Transpose" data

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.

Frequent Contributor
Posts: 99

How to "Transpose" data

Linlin &Data_null_,

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

🔒 This topic is solved and locked.

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

Discussion stats
  • 8 replies
  • 253 views
  • 7 likes
  • 4 in conversation