topic Re: add new row which will sum all row in SAS Programming

add new row which will sum all row

subrat1 — Wed, 31 Jan 2018 04:28:26 GMT

I have a table having 4 columns

Sourcetable	Count_101917	Count_102317	Count_103017
CRS	6339	6458	11914
CRS-Insight	988	1066	1065
CRS-Insight-Rater	4520	4512	4560
CRS-Rater	978	1007	1064
InInsightOnly	3367	3375	3515
InRaterAndInsight	51	37	35
InRaterOnly	5958	6052	6298

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

Sourcetable	Count_101917	Count_102317	Count_103017
CRS	6339	6458	11914
CRS-Insight	988	1066	1065
CRS-Insight-Rater	4520	4512	4560
CRS-Rater	978	1007	1064
InInsightOnly	3367	3375	3515
InRaterAndInsight	51	37	35
InRaterOnly	5958	6052	6298
Total	22201	22507	28451

Re: add new row which will sum all row

novinosrin — Wed, 31 Jan 2018 04:38:34 GMT

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

Re: add new row which will sum all row

kiranv_ — Wed, 31 Jan 2018 06:28:48 GMT

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
;

Re: add new row which will sum all row

Satish_Parida — Wed, 31 Jan 2018 06:50:44 GMT

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;

Re: add new row which will sum all row

ballardw — Wed, 31 Jan 2018 15:57:38 GMT

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.