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

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

1 ACCEPTED SOLUTION

Accepted Solutions
PGStats
Opal | Level 21

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

9 REPLIES 9
dcruik
Lapis Lazuli | Level 10

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!

PGStats
Opal | Level 21

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

PG
arunmmw
Fluorite | Level 6

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 ;

 

PGStats
Opal | Level 21

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
arunmmw
Fluorite | Level 6

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          
PGStats
Opal | Level 21

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
Cynthia_sas
SAS Super FREQ
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
Reeza
Super User
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.
arunmmw
Fluorite | Level 6

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;

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
  • 9 replies
  • 2632 views
  • 5 likes
  • 5 in conversation