Desktop productivity for business analysts and programmers

Adding new column

Accepted Solution Solved
Reply
Contributor
Posts: 47
Accepted Solution

Adding new column

I have some tables as below:

       Var1     Var2    Var3       Var4       Var5     

      AA       19,176   48.4%    20,001    49.6%

      AS        9,717    24.5%    9,609      23.8%

      BA        8,694    21.9%    8,891       22%

      BB        2,032    5,1%      1,849      4.6%

I need to add one new column at the top. And i want to sum each row. So the first new column should be like this:

       Var1       Var2     Var3       Var4       Var5    

     Sum        39,619   100.0%   40,350    100.0%

      AA         19,176   48.4%    20,001    49.6%

      AS          9,717    24.5%    9,609      23.8%

      BA          8,694    21.9%    8,891       22%

      BB          2,032    5,1%      1,849      4.6%


Accepted Solutions
Solution
‎07-30-2015 08:46 AM
Valued Guide
Posts: 858

Re: Adding new column

Here's a solution for you:

data have;

infile cards dsd dlm= '*';

informat var1 $2. var2 comma. var3 percent. var4 comma. var5 percent.;

input Var1 Var2 Var3 Var4 Var5;

cards;

AA*19,176*48.4%*20,001*49.6%

AS*9,717*24.5%*9,609*23.8%

BA*8,694*21.9%*8,891*22%

BB*2,032*5,1%*1,849*4.6%

;

run;

proc transpose data=have out=tran;id var1;var var2 var3 var4 var5; 

data prep;

set tran;

sum = sum(aa,as,ba,bb);

run;

proc transpose data=prep out=want (rename=(_NAME_ = var1));id _name_;var sum aa as ba bb;

View solution in original post


All Replies
Solution
‎07-30-2015 08:46 AM
Valued Guide
Posts: 858

Re: Adding new column

Here's a solution for you:

data have;

infile cards dsd dlm= '*';

informat var1 $2. var2 comma. var3 percent. var4 comma. var5 percent.;

input Var1 Var2 Var3 Var4 Var5;

cards;

AA*19,176*48.4%*20,001*49.6%

AS*9,717*24.5%*9,609*23.8%

BA*8,694*21.9%*8,891*22%

BB*2,032*5,1%*1,849*4.6%

;

run;

proc transpose data=have out=tran;id var1;var var2 var3 var4 var5; 

data prep;

set tran;

sum = sum(aa,as,ba,bb);

run;

proc transpose data=prep out=want (rename=(_NAME_ = var1));id _name_;var sum aa as ba bb;

Super User
Super User
Posts: 7,720

Re: Adding new column

Hi,

proc sql;

     create table WANT as

     select     "Sum" as VAR1,

                    sum(VAR2) as VAR2,

                    sum((VAR3) as VAR3,

                    sum((VAR4) as VAR4,

                    sum((VAR5) as VAR5

     from        HAVE

     union all

     select     *

     from       HAVE;

quit;

Contributor
Posts: 47

Re: Adding new column

Thank you both works

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 379 views
  • 0 likes
  • 3 in conversation