DATA Step, Macro, Functions and more

add new row which will sum all row

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

add new row which will sum all row

I have a table  having 4 columns

SourcetableCount_101917Count_102317Count_103017
CRS6339645811914
CRS-Insight98810661065
CRS-Insight-Rater452045124560
CRS-Rater97810071064
InInsightOnly336733753515
InRaterAndInsight513735
InRaterOnly595860526298

 

I want to add new row at the end which will do summation, so I want my output to be:

SourcetableCount_101917Count_102317Count_103017
CRS6339645811914
CRS-Insight98810661065
CRS-Insight-Rater452045124560
CRS-Rater97810071064
InInsightOnly336733753515
InRaterAndInsight513735
InRaterOnly595860526298
Total222012250728451

Accepted Solutions
Solution
‎01-31-2018 11:10 PM
Frequent Contributor
Posts: 112

Re: add new row which will sum all row

Correction:

proc sql;
create table new_have as
select Sourcetable ,	Count_101917,	Count_102317,	Count_103017
from have
union all
select 'Total' as Sourcetable ,	sum(Count_101917),	sum(Count_102317),	sum(count_103017)
from have
;
quit;

View solution in original post


All Replies
Super User
Posts: 2,078

Re: add new row which will sum all row

 

 

1. array, retain and sum statement

2. transpose and row sum

3. any proc sum and  output to dataset and merge back 

 

which one do you want?

 

@subrat1 I am disappointed that you don't seem to mark answers as complete in quite a few questions that you asked before. I would appreciate that courtesy

PROC Star
Posts: 549

Re: add new row which will sum all row

one way to do this 

proc sql;
select Sourcetable ,	Count_101917,	Count_102317,	Count_103017
from have
union all
select 'Total' as Sourcetable ,	sum(Count_101917),	sum(Count_102317),	sum(count_103017)
from have
;
Solution
‎01-31-2018 11:10 PM
Frequent Contributor
Posts: 112

Re: add new row which will sum all row

Correction:

proc sql;
create table new_have as
select Sourcetable ,	Count_101917,	Count_102317,	Count_103017
from have
union all
select 'Total' as Sourcetable ,	sum(Count_101917),	sum(Count_102317),	sum(count_103017)
from have
;
quit;
Super User
Posts: 13,950

Re: add new row which will sum all row

And WHY do you want this in a data set? There exists a potential when you add such rows to a data set that the same or similar code gets executed later and that total gets included in a new total?

If it is for a report that people read then very likely one of the report procedures such as Proc Tabulate, Report or even Print will do column totals.

Something like:

proc print data=have sumlabel='Total' noobs;
   var sourcetable count_101917 count_102317 count_103017;
   sum   count_101917 count_102317 count_103017;
run;

Of course you have a likely cause of more issues with the existence of your count variables which apparently have dates as part of the name. You would be better off having a data set that had variables: sourcetable dateofcount countvalue for most purposes. One of which is that when you get data from other months your report syntax does not need to change to create a readable report.

 

proc tabulate data=have;
   class sourcetable dateofcount;
   /* and if you make the dateofcount an actual SAS date value*/
   format dateofcount date9.;
   var count;
   tables sourcetable='' all='Total',
          dateofcount='' * count*sum
   ;
run;

Which report would be useable for many date periods without having to add a variable for each additional time period.

 

PLUS you could even use formats to create column totals for calendar months, quarters or years just by changing the format of the date variable.

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 156 views
  • 3 likes
  • 5 in conversation