topic Re: Transpose proc report and ods to excel in ODS and Base Reporting

Transpose proc report and ods to excel

Haha_Lun — Fri, 30 Oct 2015 16:45:52 GMT

I have a proc report and ods tagsets.excelxp to print out the following to excel.

Month	Count	Amount	Average	Average APR
5	1453	$215,132,461	$148,060.88	24.6%
6	1854	$413,651,584	$223,113.04	31.5%
7	2543	$568,413,215	$223,520.73	12.5%
8	4863	$156,487,435	$32,179.20	18.5%

Is it possible to print out the transposed result to excel with the formats?

Month	5	6	7	8
Count	1453	1854	2543	4863
Amount	$215,132,461	$413,651,584	$568,413,215	$156,487,435
Average	$148,060.88	$223,113.04	$223,520.73	$32,179.20
Average APR	24.6%	31.5%	12.5%	18.5%

Besides, I have thought of the "proc report out =" combined with "proc transpose " ,but i have hundreds of rows in real situation so it will be mess, It will be grateful if there is another way to print out the results.

Re: Transpose proc report and ods to excel

ballardw — Fri, 30 Oct 2015 17:44:46 GMT

The basic approach would be to change the role of month from a row group variabe to column.

Or possibly a different.

Show the code used to generate your current table for more specific suggestions.

Re: Transpose proc report and ods to excel

kybowma — Sat, 31 Oct 2015 17:21:12 GMT

Hello,

You could try the following...

I restructured your initial data, then used PROC REPORT and the tagattr option to achieve the formatting you were looking for. The output is somewhat rough cut, however, you could make adjustments quite easy and include a PROC FORMAT with a preloadfmt and order=data option in the define statement to order the data as you originally shown (count was first.). I would assume there is a better way to cirmcumnavigate the problem. This seemed to work.

options source;
data orig (sortedby=month);
	infile datalines dlm='|';
	input month :3.
		  count :8.
		  amount :dollar20.
		  average :dollar20.2
		  average_apr :percent9.1
	;
	length group $ 15 value 8;
	array t{4} $ 15 _temporary_ ('Count','Amount','Average','Average APR');
	array v{*} count amount average average_apr;
	i+1;
		do j=1 to dim(v);
			group=t{j};
			value=v{j};
			output;
			i=0;
		end;
datalines;
5|1453|$215,132,461|$148,060.88|24.6% 
6|1854|$413,651,584|$223,113.04|31.5% 
7|2543|$568,413,215|$223,520.73|12.5% 
8|4863|$156,487,435|$32,179.20|18.5% 
;
run;

%macro spin;
proc report data=orig nowindows missing;
	column group month, value 
%do i=1 %to 4;
		derived_value&i.
%end;
	;
	define group / group;
	define month / across;
	define value / noprint analysis;
%do i=1 %to 4;
	define derived_value&i. / computed;
	compute derived_value&i.;
		derived_value&i.=_c%eval(&i.+1)_;
		if group='Count' then call define(_col_,'style','style={tagattr="format:#####"}');
		else if group='Amount' then call define(_col_,'style','style={tagattr="format:$###,###,###"}');
		else if group='Average' then call define(_col_,'style','style={tagattr="format:$###,###,###.00"}');
		else if group='Average APR' then call define(_col_,'style','style={tagattr="format:0.0%"}');
	endcomp;
%end;

run;

%mend spin;

ods tagsets.excelxp file='<put your path here...\sas_question.xls';

%spin;

ods tagsets.excelxp close;

Re: Transpose proc report and ods to excel

Haha_Lun — Tue, 03 Nov 2015 15:55:32 GMT

Dear all,

Thanks for all your suggestions. There is a bit different on the data structure. I have thousands of data by month. Each of them has a total amount, apr and other businese information. I need to sum up the total amount, average out the total amount and calculate the weighted apr as below. In my case, I need to computed like fifty summary columns.

I understand I can use "proc report->proc transpose->ods + proc report" to finish it but it seems too mess for fifty columns. I will like to ask if there is any chance to have a shorter way?

Thanks a lot.

data orig (sortedby=month);
	infile datalines dlm=',';
	input month :3.
		  count :8.
		  amount :dollar20.
		  apr :percent9.1
	;
datalines;
5,846,153248457,0.284
5,607,61884004,0.1518
6,1154,186453156,0.296
6,700,227198428,0.330592581
7,1265,284657518,0.134
7,1278,283755697,0.115971397
8,2045,68945324,0.148
8,2818,87542111,0.214139999
;
run;

data a_1;
set orig;
weighted_apr = amount*apr;
run;

proc report data = a_1;
columns month count amount weighted_apr avg_amt avg_apr;
define month /group ;
define count/sum;
define amount/sum format=dollar20.;
define weighted_apr / sum noprint;
define avg_amt / computed format=dollar20.2 ;
define avg_apr / computed format=percent9.1 ;
compute avg_apr ;
avg_amt  = amount.sum / count.sum;
avg_apr = weighted_apr.sum/amount.sum;
endcomp;
run;