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%
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;
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;
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;
Thank you both works
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.