topic Re: How to write transpose for such data? in SAS Procedures

How to write transpose for such data?

bbb_NG — Tue, 21 Feb 2012 03:18:27 GMT

Hi,

I have a data

ID	Name	Type	Length
1	branch_code	Character	7
2	NationCMP	Character	3
3	Version	Character	6
4	ZoneCMP	Character	9
5	cmp	Character	31
6	NoCount	Numeric	8

which is designed for excel pivot table, for field 1-4 are already-grouped-by field,

field 5 cmp has a lot of values to be distinguished in Pivot table as Row,

for eg. with value 'Male','Female' as Sex classification and 'Commercial','Production' as Occupation classification,

'saving account','Term deposit' as account type.

and field 6 nocount are the count/sum respective to field cmp, say

cmp=male, nocount=9,

cmp=saving account, nocount=15235.76

This format serves my purpose to generate Excel pivot, but it is vertically duplicated.

I want to use transpose to re-write the format as follows, but with half day try failed, please give a hand.

branch_code

NationCMP

Version

ZoneCMP

Male

Female

saving account

Term deposit

Commercial

Production

for field 1-4 no transpose, to set values for cmp to the field name, to set respective values for nocount as value under the field.

Thus to avoid duplication.

Thanks.

How to write transpose for such data?

art297 — Tue, 21 Feb 2012 03:41:52 GMT

Are those six values, in the mixed cases as shown, the only six values in the variable cmp?

How to write transpose for such data?

bbb_NG — Tue, 21 Feb 2012 03:48:32 GMT

Dear Art,

In fact I'm shiftless.

I meant that field1-4 are grouped-by fields, infact there are 10 grouped-by fields.

and in cmp there are about 60 values.

the Descartes made the data so big but with duplicated records in 10 grouped-by fields.

I have 4M records in this format, I suppose after transpose it will shrink to 10k-100k records but with more fields but no duplication.

Thanks.

Re: How to write transpose for such data?

Tom — Tue, 21 Feb 2012 04:59:59 GMT

Can you post some actual records and how you want them transposed?

I think it sounds like you have data where one variable has the variable name (Male or Female) and another variable has the value (Count?). This is perfect for PROC TRANSPOSE.

proc transpose data=have out=want ;

by grp var ;

var count ;

name zone;

run;

You probably have data for many variables so that you might get a diagonal matrix.

Some thing like.

GRP Var Male Female Savings

1 Gender 10 20 .

1 Savetype . . 1002

2 Gender 12 18 .

2 Savetype . . 2345

You should be able to use the fact that the off terms are all missing to use the UPDATE statement to fix that.

data fix;

update want(obs=0) want ;

by grp ;

drop var;

run;

Re: How to write transpose for such data?

bbb_NG — Tue, 21 Feb 2012 06:22:51 GMT

Tom，

Thanks for your reply.

I'm now digesting it.

For your question ,I have data

branch_code	PFS Cust Segment Code	LoanCMP	OutstandingCMP	NationCMP	Version	New Cust Ind	OccupationCMP	TRBLevel	ZoneCMP	cmp	NoCount
AFU	NORMAL	N	N	CN	201201	N	Commercial	TRB1:0_10K	Zone 1	Male	1
AFU	NORMAL	N	N	CN	201201	N	Commercial	TRB1:0_10K	Zone 2	Female	2
AFU	NORMAL	N	N	CN	201201	N	Commercial	TRB1:0_10K	Zone 1	Commercial	3
AFU	NORMAL	N	N	CN	201201	N	Commercial	TRB1:0_10K	Zone 1	Production	4
AFU	NORMAL	N	N	CN	201201	N	Commercial	TRB1:0_10K	Zone 1	Saving Account Balance	15,763.67

field branch_code to ZoneCmp are all grouped already.

and I want the output

branch_code

PFS Cust Segment Code

LoanCMP

OutstandingCMP

NationCMP

Version

New Cust Ind

OccupationCMP

TRBLevel

ZoneCMP

Male

Female

Commercial

Production

Saving Account Balance

AFU

NORMAL

201201

Commercial

TRB1:0_10K

Zone 1

15,763.67

It's the real data.

Thanks

Re: How to write transpose for such data?

art297 — Tue, 21 Feb 2012 06:40:01 GMT

I think that the following does what you want:

proc summary data=have nway;

class branch_code nationcmp

/*and any other grouping fields*/

cmp;

var NoCount;

output out=temp (drop=_:) sum=;

run;

proc transpose data=temp

out=want (drop=_:);

by branch_code nationcmp

/*and any other grouping fields*/;

id cmp;

var NoCount;

run;

How to write transpose for such data?

shivas — Tue, 21 Feb 2012 06:49:39 GMT

Hi ,

Is this what your are expecting,

data a;

input branch_code $ PFSCustSegmentCode $ LoanCMP $ OutstandingCMP $ NationCMP $ Version $ NewCustInd $ OccupationCMP $ 13. TRBLevel $ 13. ZoneCMP $ 7. cmp $ 22. NoCount;

cards;

AFU NORMAL N N CN 201201 N Commercial TRB1:0_10K Zone 1 Male 1

AFU NORMAL N N CN 201201 N Commercial TRB1:0_10K Zone 2 Female 2

AFU NORMAL N N CN 201201 N Commercial TRB1:0_10K Zone 1 Commercial 3

AFU NORMAL N N CN 201201 N Commercial TRB1:0_10K Zone 1 Production 4

AFU NORMAL N N CN 201201 N Commercial TRB1:0_10K Zone 1 SavingAccountBalance 15763.67

;

run;

proc sort data=a out=b;by branch_code PFSCustSegmentCode LoanCMP OutstandingCMP NationCMP Version NewCustInd OccupationCMP TRBLevel ZoneCMP;run;

proc transpose data=b out=c ;

by branch_code PFSCustSegmentCode LoanCMP OutstandingCMP NationCMP Version NewCustInd OccupationCMP TRBLevel ZoneCMP;

id cmp ;

var NoCount;

run;

How to write transpose for such data?

bbb_NG — Tue, 21 Feb 2012 06:52:59 GMT

Dear All,

Thank you all for spending time looking at my question and giving an answer.

Very thank you.

How to write transpose for such data?

bbb_NG — Tue, 21 Feb 2012 09:31:29 GMT

Dear all,

I've write the code as you mentioned above,

first sort, then transpose.

but error occurs

ERROR: ID value“'TRB9_4:300k_1M'n” appears twice in the same By group.

Note, that point to the following by group;

BRANCH CODE=AFU 'PFS Cust Segment Code'n=PSE LoanCMP=N OutstandingCMP=N NationCMP=CN Version=201201 New Cust Ind=N.

Then I tried debug, using filters as "BRANCH CODE=AFU 'PFS Cust Segment Code'n=PSE LoanCMP=N OutstandingCMP=N NationCMP=CN Version=201201 New Cust Ind=N", and only to find that there's no records for CMP=''TRB9_4:300k_1M'n', which means that the error might attributed to that

I've listed values for cmp ,which accounts for 135 items.

and for the already-grouped records, may only have 100 or even less of the item in vertical duplicate format.

Is the missing values caused the faliure of this transpose function?

How to write transpose for such data?

bbb_NG — Tue, 21 Feb 2012 09:37:17 GMT

By the way,anyone knows how to let the log using english only, my SAS EG 4.2 is with english panel, but the log is combined with english and chinese, thus I have to translate the error, not the original ones.

How to write transpose for such data?

shivas — Tue, 21 Feb 2012 09:47:13 GMT

Hi ...

use LET option in transpose..

proc transpose data=b out=c let;

by branch_code PFSCustSegmentCode LoanCMP OutstandingCMP NationCMP Version NewCustInd OccupationCMP TRBLevel ZoneCMP;

id cmp ;

var NoCount;

run;

How to write transpose for such data?

Tom — Tue, 21 Feb 2012 13:55:43 GMT

Unless you have mistyped the value in the second row you should not include the variable ZONECMP in the proc transpose call. Your example input has two values for this variable and your desired output only has one row.

Based on your example rows there should not be rows of data where CMP is like TRB..... Those values look like the values that are in the variable TRBLevel. Check and make sure that you are really dealing with a single range of values in your source data. One of the problems when trying to deal with Excel files is that it is totally free form and so the values in the sheets might not form nice rectangular tables that SAS or a database system can handle. You might need to treat the sheet as being composed of more than one block of data.

The other issue about why you might not be able to find a match for the string that you see in the error message is that SAS might remove leading spaces before the value. You might try searching for just pieces of the values listed in the log. For example: where index(cmp,'TRB') and index(cmp,'4:300').

How to write transpose for such data?

art297 — Tue, 21 Feb 2012 14:15:30 GMT

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.

How to write transpose for such data?

bbb_NG — Wed, 22 Feb 2012 02:08:23 GMT

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.

How to write transpose for such data?

bbb_NG — Wed, 22 Feb 2012 02:10:46 GMT

Shivas,

Thanks a lot.

After using Let, the error appears no more.

I'm not working on the checking procedure.

If nothing wrong, I will mark it as correct.

Very thanks.

How to write transpose for such data?

bbb_NG — Wed, 22 Feb 2012 02:12:15 GMT

Tom,

Thanks for your high level instruction. I 'm checking my mistakes under your instruction.

How to write transpose for such data?

art297 — Wed, 22 Feb 2012 02:43:13 GMT

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.

How to write transpose for such data?

bbb_NG — Wed, 22 Feb 2012 02:57:31 GMT

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.

How to write transpose for such data?

bbb_NG — Wed, 22 Feb 2012 03:05:02 GMT

Shivas,

Thanks,

a little "Let" can do a so great work.

thanks.