topic dynamically summarizing rows and columns in SAS Programming

dynamically summarizing rows and columns

arunmmw — Tue, 20 Oct 2015 17:15:30 GMT

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

Re: dynamically summarizing rows and columns

dcruik — Tue, 20 Oct 2015 18:05:54 GMT

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!

Re: dynamically summarizing rows and columns

PGStats — Tue, 20 Oct 2015 18:45:30 GMT

union all will give the same result, but a bit faster, as it won't try to remove duplicate lines.

Re: dynamically summarizing rows and columns

arunmmw — Tue, 20 Oct 2015 19:25:30 GMT

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 ;

Re: dynamically summarizing rows and columns

PGStats — Tue, 20 Oct 2015 20:05:31 GMT

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.

Re: dynamically summarizing rows and columns

arunmmw — Tue, 20 Oct 2015 21:25:04 GMT

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

Re: dynamically summarizing rows and columns

PGStats — Tue, 20 Oct 2015 21:44:19 GMT

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.

Re: dynamically summarizing rows and columns

Cynthia_sas — Wed, 21 Oct 2015 00:49:15 GMT

Hi, As PGStats suggested, there are many other procedures that SAS has, like REPORT, TABULATE and even PRINT that will do sub-totals and grand totals and column totals without needing queries or remerging the summary numbers back with the rows. I'd investigate those instead of using SQL. Given the structure of your data, PROC REPORT might be better. If the structure of your data are different than possibly TABULATE would be better.

cynthia

Re: dynamically summarizing rows and columns

Reeza — Wed, 21 Oct 2015 04:51:22 GMT

Do you have any naming convention with your variables or will they be random? If you have naming conventions you can use variable shortcuts within the various procs.

Re: dynamically summarizing rows and columns

arunmmw — Wed, 21 Oct 2015 13:52:45 GMT

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;