topic Re: USING TRANSPOSE in New SAS User

USING TRANSPOSE

Kirito1 — Wed, 26 Apr 2023 09:08:54 GMT

I was working on a data as below:

MemberReference	AccountType	COUNT_F	TOTAL_AMOUNT
21121609	Business Loan - General	1	256953
21122059	Gold Loan	1	218399
21122566	Business Loan - General	1	289160
21122975	Property Loan	1	819621
21123078	Gold Loan	1	426088
21123230	Loan Against Bank Deposits	1	632405
21123300	Business Loan - Priority Sector - Agriculture	1	466791
21123300	Business Loan - Secured	1	288177
21123922	Property Loan	1	575040
21125025	Property Loan	1	722551
21125826	Property Loan	2	2242058
21126364	Overdraft	1	1625360
21126372	Overdraft	1	689969
21126801	Property Loan	1	480000
21126826	Business Loan - Priority Sector - Agriculture	1	464286
21127646	Property Loan	1	736105

I need the following results as below(Sample Screenshot):

MemberReference

Business Loan - General

Gold Loan

Property Loan

Loan Against Bank Deposits

Business Loan - Priority Sector - Agriculture

Business Loan - Secured

Overdraft

MemberReference with their respective Count under it.

So for that I was doing the following SAS code.

proc summary Data = NAS_QR.SUMBB2 NWAY;
class MemberReference AccountType;
VAR COUNT CurrentBalance;
output out = TT1(drop = _TYPE_ _FREQ_)
SUM(COUNT) = COUNT_F SUM(CurrentBalance) = TOTAL_AMOUNT;
run;

proc transpose data = TT1 out= TT2;
by MemberReference;
id AccountType;
var COUNT_F;
run;

But it is showing error as below:

ERROR: The ID value "'Business Loan - Priority Sector'n" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      MemberReference=21168838
ERROR: The ID value "'Business Loan - Priority Sector'n" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      MemberReference=21191289
ERROR: The ID value "'Business Loan - Priority Sector'n" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      MemberReference=21277591
ERROR: The ID value "'Business Loan - Priority Sector'n" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      MemberReference=21428025
ERROR: The ID value "'Business Loan - Priority Sector'n" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      MemberReference=21877507
ERROR: The ID value "'Business Loan - Priority Sector'n" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      MemberReference=21886501
ERROR: The ID value "'Business Loan - Priority Sector'n" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      MemberReference=22118825
ERROR: The ID value "'Business Loan - Priority Sector'n" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      MemberReference=22121316
ERROR: The ID value "'Business Loan - Priority Sector'n" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      MemberReference=22135069
ERROR: The ID value "'Business Loan - Priority Sector'n" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
      MemberReference=22150184
ERROR: The ID value "'Business Loan - Priority Sector'n" occurs twice in the same BY group.
ERROR: Too many bad BY groups.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 7004 observations read from the data set WORK.TT1.
WARNING: The data set WORK.TT2 may be incomplete.  When this step was stopped there were 0 observations and 0 variables.
WARNING: Data set WORK.TT2 was not replaced because this step was stopped.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      
81    
82    OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
95

What should I do to achieve the desired output?

Please, help.

Re: USING TRANSPOSE

Kurt_Bremser — Wed, 26 Apr 2023 10:00:25 GMT

This is no task for TRANSPOSE, but rather for PROC REPORT, where you can summarize multiple entries fot the same loan type.

proc report data=NAS_QR.SUMBB2;
column member_reference count_f,account_type;
define member_reference / group;
define count_f / "" analysis sum;
define account_type / "" across;
run;

Re: USING TRANSPOSE

PaigeMiller — Wed, 26 Apr 2023 10:16:46 GMT

And by using PROC REPORT, you can wind up with header names that don't require the rather difficult to type

"'Business Loan - Priority Sector'n"

in fact you don't have to do anything special to get a header name of

Business Loan - Priority Sector

without the quotes and without the N at the end. You could also easily get a header name of

Business Loan
Priority Sector

on two lines. So don't use PROC TRANSPOSE here, that's not what PROC TRANSPOSE is for.

Re: USING TRANSPOSE

Kirito1 — Wed, 26 Apr 2023 10:18:05 GMT

But what if I want to use the report result as table for further things.

Re: USING TRANSPOSE

Kirito1 — Wed, 26 Apr 2023 11:15:02 GMT

While I was finding out what should I use to do this type of task on listendata.com where it was giving an example of student, marks and subject. So I relate that Example with my task where Student is MemberReference, Marks are COUNT_F and Subject is AccountType.

Kindly, refer the link This is how I want to approach this problem.

Transpose

Re: USING TRANSPOSE

PaigeMiller — Wed, 26 Apr 2023 12:28:19 GMT

@Kirito1 wrote:
But what if I want to use the report result as table for further things.

Be specific. What other things would require a TRANSPOSE but not a REPORT? What is the future step and/or analysis which requires the data to be transposed? We would need to know this to give you the best advice.

Re: USING TRANSPOSE

Kurt_Bremser — Wed, 26 Apr 2023 11:33:48 GMT

With rare exceptions (regression), the long layout is better for analysis. A wide layout, combined with non-standard column names, is mostly a PITA.