Hi,
Here is a table structure.
e.g.
date | test | tes1 | col2 | col4 |
1/3/2015 | 4 | 2 | 0 | 1 |
4/5/2015 | 2 | 7 | 9 | 0 |
I want to dynamically create a column and row that summarizes these values by column and row. something like shown below.
date | test | tes1 | col2 | col4 | total |
1/3/2015 | 4 | 2 | 0 | 1 | 7 |
4/5/2015 | 2 | 7 | 9 | 0 | 18 |
Total | 6 | 9 | 9 | 1 | 25 |
right now i am planning on getting this done with multiple proc sql's is there any way i can do a macro coding to get this done
IMHO, you are doing your summarizing in the wrong place. These summaries (totals) are meant for reporting and should be generated dynamically by reporting procedures (e.g. PRINT, REPORT, TABULATE) at report generation time.
You can do it in one step of a SQL procedure. This is assuming you're date column is already converted into a character field:
data have;
input date$ test tes1 col2 col4;
datalines;
1/3/2015 4 2 0 1
4/5/2015 2 7 9 0
;
run;
proc sql;
create table want as
select date,
test,
tes1,
col2,
col4,
sum(test,tes1,col2,col4) as total
from have
union
select "Total" as date,
sum(test) as test,
sum(tes1) as tes1,
sum(col2) as col2,
sum(col4) as col4,
sum(sum(test,tes1,col2,col4)) as total
from have;
quit;
Hope this is helps!
union all will give the same result, but a bit faster, as it won't try to remove duplicate lines.
Thanks for your reply. My questions should have been framed in a different way. I want to add grand total for column and rows - however these columns might grow based on data that feeds into the table, and I may not know the column names.
if the table size grows to 50 columns with different names, i shouldn't rewrite the code. We do this right now with pivot tables. Something close to what i want is in this example from another site.
data output ;
set input end=eof;
array M(*) M2014: ;
array F(*) _temporary_ ;
* Create grand total column ;
grand_total=sum(of m(*)) ;
output ;
* Output grand total row ;
if eof then do ;
do i=1 to dim(m) ;
M(i)=F(i) ;
end ;
output ;
end ;
run ;
IMHO, you are doing your summarizing in the wrong place. These summaries (totals) are meant for reporting and should be generated dynamically by reporting procedures (e.g. PRINT, REPORT, TABULATE) at report generation time.
You are right - ideally summarization should be at report level. Although, I was able to get grand total at row level using SQL joins, couldnt find an easier solution to summarize at column level
Is it really that hard to create summary at column level.
RCVD_DT | c_C31 | c_C72 | c_C73 | c_C36 | c_C55 |
30Sep2015 | 1 | ||||
01Oct2015 | 1 | ||||
19Oct2015 | 1 | 52 | 15 | ||
20Oct2015 | |||||
Grand Total |
SQL doesn't support variable lists the way DATA steps do. So, no it is not hard to summarize at column level, but it requires a lot of typing in SQL queries. The only alternative is to use the SAS macro facility to generate the required variable lists.
There are no naming conventions for this table except that the total column name length is 3.
Update:i did use proc tabulate as SAS experts here suggested, and excel ods tagsets to create output to look exactly how it was when using pivot table instead of enterprise guide excel output
Although, it would be cool to create a macro to come up with summarizing rows/columns may be using dictionary.columns
ods tagsets.excelxp file='/test/test/test.xml'
style=Printer;
Title 'test';
ods tagsets.excelxp
options(Sheet_name = 'test');
proc tabulate data=test out=testxx ;
class datevar statusvar;
keylabel all = Total sum=' ' ;
var id;
table datevar ALL, (statusvar all ) * id* SUM * f=comma9.0 ;
LABEL id = '.';
run;
ods tagsets.excelxp close;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.