topic How to "Transpose" data in SAS Programming

How to "Transpose" data

bbb_NG — Thu, 09 Feb 2012 06:24:05 GMT

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?

How to "Transpose" data

ArtC — Thu, 09 Feb 2012 06:42:18 GMT

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;

How to "Transpose" data

bbb_NG — Thu, 09 Feb 2012 06:59:20 GMT

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

Re: How to "Transpose" data

Linlin — Thu, 09 Feb 2012 14:13:45 GMT

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

Re: How to "Transpose" data

ArtC — Thu, 09 Feb 2012 16:43:10 GMT

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.

How to "Transpose" data

data_null__ — Thu, 09 Feb 2012 19:23:11 GMT

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.

How to "Transpose" data

Linlin — Thu, 09 Feb 2012 20:26:12 GMT

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

How to "Transpose" data

bbb_NG — Fri, 10 Feb 2012 02:27:35 GMT

Linlin &Data_null_,

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

Re: How to "Transpose" data

ArtC — Fri, 10 Feb 2012 06:16:13 GMT

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.