DATA Step, Macro, Functions and more

dynamically summarizing rows and columns

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

dynamically summarizing rows and columns

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


Accepted Solutions
Solution
‎10-21-2015 09:43 AM
Respected Advisor
Posts: 4,640

Re: dynamically summarizing rows and columns

[ Edited ]

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.

PG

View solution in original post


All Replies
Frequent Contributor
Posts: 128

Re: dynamically summarizing rows and columns

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!

Respected Advisor
Posts: 4,640

Re: dynamically summarizing rows and columns

[ Edited ]

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

PG
Occasional Contributor
Posts: 17

Re: dynamically summarizing rows and columns

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 ;

 

Solution
‎10-21-2015 09:43 AM
Respected Advisor
Posts: 4,640

Re: dynamically summarizing rows and columns

[ Edited ]

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.

PG
Occasional Contributor
Posts: 17

Re: dynamically summarizing rows and columns

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          
Respected Advisor
Posts: 4,640

Re: dynamically summarizing rows and columns

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 Smiley Happy in SQL queries. The only alternative is to use the SAS macro facility to generate the required variable lists.

PG
SAS Super FREQ
Posts: 8,739

Re: dynamically summarizing rows and columns

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
Super User
Posts: 17,730

Re: dynamically summarizing rows and columns

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.
Occasional Contributor
Posts: 17

Re: dynamically summarizing rows and columns

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;

☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 471 views
  • 5 likes
  • 5 in conversation