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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.