topic Re: Need help on proc transpose in SAS Procedures

Need help on proc transpose

RamKumar — Tue, 09 Sep 2014 08:07:00 GMT

I need help on proc tranpose. My data is as below.

cust account balance

Smith checking        $1,000.00
Smith Savings        $4,000.00
Smith mortgage      $150,000.00
Smith credit_card    $500.00
Jones checking       $973.78
Jones savings         $2,613
Jones Mortgage      .
Jones credit_card   $140.48

I need to convert as below. I did tried, but trouble displaying the varaibles as below.So I request someone to suggest me here.

cust checking saving mortgage credit_card

smith $1,000.00 $4,000.00 $150,000.00 $500.00
Jones $973.78 $2,613 . $140.48

On a seperate note, I need to find a correlation between each account (between checking and saving, mortgage and credit card etc) to find out which is sold more.

In that case, is that possible to apply proc corr between the accounts?

Re: Need help on proc transpose

RamKumar — Tue, 09 Sep 2014 10:24:39 GMT

I'm using proc corr to find out the relationship between account. It means how 'checking' was sold when compared to the other accounts like saving, mortgage and credit_card.

Please let me know if we've any other better solution for this apporoach.

Re: Need help on proc transpose

MSaber — Tue, 09 Sep 2014 12:19:22 GMT

Dear RamKumar,

find below the answer:

1- for transpose the following example for your data:

data test;

input cust $ account $ balance:comma12.2;

cards;

Smith checking $1,000.00

Smith Savings $4,000.00

Smith mortgage $150,000.00

Smith credit_card $500.00

Jones checking $973.78

Jones savings $2,613

Jones Mortgage .

Jones credit_card $140.48

;

run;

PROC SORT

DATA=WORK.TEST(KEEP=balance account cust)

OUT=WORK.SORTTempTableSorted

;

BY cust;

RUN;

PROC TRANSPOSE DATA=WORK.SORTTempTableSorted

OUT=WORK.TRNSTransposedTEST(LABEL="Transposed WORK.TEST")

LET

NAME=Source

LABEL=Label

;

BY cust;

ID account;

VAR balance;

/* -------------------------------------------------------------------

End of task code.

------------------------------------------------------------------- */

RUN; QUIT;

2- Proc Cor is the best procedure to calculate the correlation as you need.

Re: Need help on proc transpose

data_null__ — Tue, 09 Sep 2014 12:23:57 GMT

Assuming balance is numeric and formatted with DOLLAR format it will be associated with the new variables when transposed. You also need to "standardize" all the values of ACCOUNT. I used UPCASE.

data cust;
   infile cards expandtabs; 
   input cust $ account :$upcase16.   balance :comma12.; 
   format balance dollar12.2; 
   cards; 
Smith  checking        $1,000.00 
Smith  Savings        $4,000.00 
Smith  mortgage      $150,000.00 
Smith  credit_card    $500.00 
Jones  checking       $973.78 
Jones  savings         $2,613 
Jones  Mortgage      .
Jones  credit_card   $140.48
;;;;
   run; 
proc print; 
   run; 
proc transpose out=wide;
   by cust notsorted; 
   id account;
   var balance;
   run; 
proc print; 
   run;

Re: Need help on proc transpose

Hima — Tue, 09 Sep 2014 12:46:20 GMT

data have ;
input cust $ account $15. balance ;
   format balance dollar9.2;
informat balance dollar9.2;
cards ;
Smith checking         $1,000.00
Smith Savings          $4,000.00
Smith mortgage       $150,000.00
Smith credit_card        $500.00
Jones checking           $973.78
Jones savings             $2,613
Jones Mortgage                 .
Jones credit_card        $140.48
;
run;

proc sort data=have;
by cust;
run;

proc transpose data=have out=want;
    by cust ;
    id account;
    var balance;
run;

proc print data = want;
run;

Re: Need help on proc transpose

RamKumar — Tue, 09 Sep 2014 13:36:18 GMT

Thanks everyone, just I wondering for the response on 'proc corr'.

I need to find out each account goes with other account (how many customers brought savings account along with Mortgage account). In proc corr I can correlate only with two variables, but I wish to correlate with 4 variables here.

Is any other alternate procedure or other function/queries available for my approach?

Re: Need help on proc transpose

stat_sas — Tue, 09 Sep 2014 13:47:27 GMT

If you are looking for correlation of one variable with remaining variables. This may be helpful.

proc corr data=have;

var checking saving mortgage credit_card;

run;

Re: Need help on proc transpose

RamKumar — Tue, 09 Sep 2014 14:19:56 GMT

Thanks for the code. When I ran your code with some additional data, I got the output as below.

Pearson Correlation Coefficients

Prob > |r| under H0: Rho=0

Number of Observations

CREDIT_

CHECKING SAVINGS MORTGAGE CARD

CHECKING 1.00000 1.00000 . 1.00000

. . . .

2 2 1 2

SAVINGS 1.00000 1.00000 . 1.00000

. . .

2 3 1 2

MORTGAGE . . 1.00000 .

. . . .

1 1 2 1

With this output I couldn't figure out whether we have positive or negative correlation. Please suggest me over here.

Re: Need help on proc transpose

stat_sas — Tue, 09 Sep 2014 14:44:55 GMT

Not sure, If you can share some data then it would be more clear.

Re: Need help on proc transpose

data_null__ — Tue, 09 Sep 2014 14:47:36 GMT

Yes indeed! and then perhaps could help with the statistics.

Re: Need help on proc transpose

MSaber — Wed, 10 Sep 2014 05:26:04 GMT

if you will have negative correlation you will see the negative numbers. but it seems your data with positive correlations

Re: Need help on proc transpose

RamKumar — Wed, 10 Sep 2014 10:09:19 GMT

I've created a new thread https://communities.sas.com/message/227831#22783 instead of mess up with the current one which relates to transpose.