How to write transpose for such data?

[art297]

The reason I suggested doing a proc summary before you used proc transpose was, from your original description, I understood that you expected to have multiple records for each of your class variables.  The purpose of using proc summary was to combine those records, by summing the values, prior to transposing the file.

[bbb_NG]

Art,

Thank your for your remind.

I have suffered the following phases.

1.Broken data in several data-views in the database.

2.Consolidated records (with PK) merged with information needed say, adding Gender/Profit,etc.

3.Using Proc SQL for generating data set by grouping classes + CMP+ Value for CMP

4.Union all those from phase 3, generating a vertical duplicated data.

5.Exporting to txt file.

6.Using Excel Pivot table wizard, all grouped fields in Page level, 'CMP' in Row level, Value for cmp in Data using sum function.

from step 1-6, I achieved my purpose as supporting role.

and after setp 4, the records number reaches 4M. and I want to do some enhancement for efficiency sake.

and I don't want to start from setp 1 again to generate a data with grouped value but not vertical showing ,but with horizatal showing, which means a lot of coding again.

thus I tried to just begin after step 4 to use transpose to get my desired format.

as to how to treat them to be readable in Excel pivot , I 'm not having a whole solution.

[art297]

My opinion?  Too many seemingly disjointed steps.

I would much rather have one stream of code that can be verified.  Sometimes, starting over is the best way to solve a problem.

[bbb_NG]

Art,

     Thanks for your opinion.

     To have a good start and a good construction of the whole project is very important, which i can not agree more.

     I was graduated from computer science.I was training to group code for differenct purpose into moudles.

     The advantange is obvious,  if I can not write code without error, this method will be easy when conduct debug.

     And from you I learned to conduct a good framework before starting is important.

     Thanks for sharing your thoughts.