BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
subrat1
Fluorite | Level 6

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
1 ACCEPTED SOLUTION

Accepted Solutions
Satish_Parida
Lapis Lazuli | Level 10

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

4 REPLIES 4
novinosrin
Tourmaline | Level 20

 

 

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

kiranv_
Rhodochrosite | Level 12

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
;
Satish_Parida
Lapis Lazuli | Level 10

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;
ballardw
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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