CODEWITHSUNDEEP
sastransposeproc
mustafghan
mustafghan

Reputation: 171

SAS proc transpose

I have the following dataset

Account 1       Account 2       Account1Amount    Account2Amount 
A.              B.              25.               55
F.              D.              10.               70

I get the following when I transpose by Account 1 and Account 2

      Amount 1.        Amount 2.        Col1
           25.                    55.                   A
           25.                    55.                   B
           10.                    70.                   F
           10.                    70.                   D

But I want the following

    Amount 1.        Amount 2.        Col1
           25.                    0                      A
           0.                     55.                   B
           10.                    0                      F
           0.                      70.                   D

In other words, I don't want the amounts to be double counted for each account since it messes up my true total across different accounts. This is also a sample of the data, I actually have 185 rows.

I just don't know how to go about solving this. Is there an option is proc transpose that I can use, or perhaps do the transpose and then fix the issue. But I just can't wrap my head around going about solving this. Any help would be appreciated.

Upvotes: 0

Views: 626

Answers (2)

Kiran
Kiran

Reputation: 3315

one more way, if you have few columns is to 

   proc sql;
   select Account_1,  Account1Amount, 0  from 
   have 
   union
   select Account_2, 0, Account2Amount from 
   have b;

Upvotes: 0

Richard
Richard

Reputation: 27498

The original data set is what I would consider atypical. It appears to contain two parallel sets of information without any explicit key.

data have;
input
Account_1 $     Account_2 $     Account1Amount    Account2Amount; datalines;
A.              B.              25.               55
F.              D.              10.               70
run;

The stated result appears to be from this transpose:

proc transpose data=have out=got;
  by account_1 account_2;
  var account1amount account2amount;
run;

Transposing the parallel data as stated is probably easiest using a data step arrays and output statements. Going with only transpose would involve two transposes and set stacking the outputs.

data want_stated;
  set have;
  array accounts account_1 account_2;
  array amounts account1amount account2amount;
  array amount amount1 amount2;

  col1 = accounts(1);
  amount(1) = amounts(1);
  amount(2) = 0 ;

  output;

  col1 = accounts(2);
  amount(1) = 0;
  amount(2) = amounts(2);

  output;
run;

A more use useful normal categorical transposed form is probably the account/amount vector (as mentioned by Tom)

data want_maybe;
  set have;
  array accounts account_1 account_2;
  array amounts account1amount account2amount;
  group = _n_;
  do _n_ = 1 to dim(accounts);
    account = accounts(_n_);
    amount = amounts(_n_);
    output;
  end;
  keep group account amount;
run;

Upvotes: 0

Related Questions