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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: