topic Re: proc report: summarize across columns within another across variable in SAS Programming

proc report: summarize across columns within another across variable

ciro — Mon, 09 Nov 2020 18:22:24 GMT

Dear all,

I am trying to produce a report that have nested across variables and within the first I need a summary column of the second variable.

the dataset is something like:

data a;
g='A'; year=1900; sex='M'; value=5; w=10; output;
g='A'; year=2000; sex='M'; value=2; w=15; output;
g='A'; year=1900; sex='F'; value=3; w=10; output;
g='A'; year=2000; sex='F'; value=1; w=15; output;
g='B'; year=1900; sex='M'; value=7; w=10; output;
g='B'; year=2000; sex='M'; value=4; w=15; output;
g='B'; year=1900; sex='F'; value=4; w=10; output;
g='B'; year=2000; sex='F'; value=2; w=15; output;
run;

First of all, I need to write an equivalent proc report code for the following proc tabulate:

proc tabulate data=a;
class g year sex;
var value;
weight w;
tables g='' all='Total', year=''*(sex='' all='T')*value=''*sum=''*f=8.0;
run;

moreover i need three more columns that represent the change between 1900 and 2000 for each sex and total

I have tried with across variables and compute blocks but with no success.

Thank you very much in advance

Re: proc report: summarize across columns within another across variable

ballardw — Mon, 09 Nov 2020 18:35:42 GMT

@ciro wrote:

Dear all,

I am trying to produce a report that have nested across variables and within the first I need a summary column of the second variable.

the dataset is something like:

data a;
g='A'; year=1900; sex='M'; value=5; w=10; output;
g='A'; year=2000; sex='M'; value=2; w=15; output;
g='A'; year=1900; sex='F'; value=3; w=10; output;
g='A'; year=2000; sex='F'; value=1; w=15; output;
g='B'; year=1900; sex='M'; value=7; w=10; output;
g='B'; year=2000; sex='M'; value=4; w=15; output;
g='B'; year=1900; sex='F'; value=4; w=10; output;
g='B'; year=2000; sex='F'; value=2; w=15; output;
run;

First of all, I need to write an equivalent proc report code for the following proc tabulate:

proc tabulate data=a;
class g year sex;
var value;
weight w;
tables g='' all='Total', year=''*(sex='' all='T')*value=''*sum=''*f=8.0;
run;

moreover i need three more columns that represent the change between 1900 and 2000 for each sex and total

I have tried with across variables and compute blocks but with no success.

Thank you very much in advance

Really should share the proc report code attempted.

Across usually means that you have to use column references, the _c1_ , _c2_ column values.

I will admit to being too lazy to write such report code and generally use Proc Summary and maybe a data step to generate the needed values.

Plus multiple nested columns is not Proc Report's strong suit.

Re: proc report: summarize across columns within another across variable

PaigeMiller — Mon, 09 Nov 2020 18:52:24 GMT

So, I'm in the same rowboat as @ballardw . I'd do the arithmetic in PROC SUMMARY and then use PROC REPORT for the report itself, although I'm sure it is possible to do this entirely in PROC REPORT.

proc summary data=a;
	class sex year g;
	var value;
	weight w;
	output out=_sums_ sum=;
run;
data _sums_;
	set _sums_;
	if missing(sex) then sex='Z';
	if missing(g) then g='Z';
run;
proc format;
	value $zf 'Z'='Total';
run;
proc report data=_sums_;
	columns g year,sex,value;
	define g/group format=$zf5. ' ';
	define year/across ' ';
	define sex/across ' ' format=$zf5.;
	define value/sum ' ';
run;

Re: proc report: summarize across columns within another across variable

ciro — Tue, 10 Nov 2020 09:05:39 GMT

Hi,

thank you for the suggestions. I know i can do the arithmentics in proc summary and then proc report the results.

however I will have to write frequently this kind of code and hope to learn to use proc report.

one of my best attempts was the following:

proc report data=a nowd;
columns g value,year,sex value,year;
define g /group ;
define value /analysis sum weight=w f=8.0;
define year /across '';
define sex /across '';
run;

but the result is not what I want, as i need the total over sex within the year.

I hope some proc report maven can help.

Re: proc report: summarize across columns within another across variable

PaigeMiller — Tue, 10 Nov 2020 12:40:25 GMT

Certainly that's your choice to do this entirely in PROC REPORT.

However, both @ballardw and I think there is benefit in doing the calculations in PROC SUMMARY, perhaps you should re-consider that approach as being a valuable approach.

Re: proc report: summarize across columns within another across variable

ciro — Tue, 17 Nov 2020 15:43:51 GMT

Hi,

after some attempts here is my result.

I was not able to get the total over sex within year with a compute block, but I managed by creating a dummy variable in the dataset.

I enclose the code so that readers can apply either the solution suggested by PaigeMiller and ballardw or mine. (I get the word 'Total' truncated, do not know why, but it's a minor thing for me).

thank you guys.

data have;
set a;
dummy='T';
run;

proc report data=have nowd;
columns g value,year,(sex dummy) diff,(sex dummy);
define g /group width=5;
define value /analysis sum weight=w f=8.0 '';
define year /across '';
define sex /across '';
define dummy /across '';
define diff /computed '' f=8.1 '% Change';
compute g ;
if _break_='_RBREAK_' then g='Total';
endcomp;
rbreak after / summarize;

compute diff ;
_c8_=100*(_c5_-_c2_)/_c2_;
_c9_=100*(_c6_-_c3_)/_c3_;
_c10_=100*(_c7_-_c4_)/_c4_;
endcomp;

run;

Re: proc report: summarize across columns within another across variable

Cynthia_sas — Tue, 17 Nov 2020 20:23:19 GMT

Hi:

If all you want to do is duplicate the PROC TABULATE output, you can do that with PROC REPORT, as shown below:

Although it might be better to use PROC MEANS to summarize your data and create the weighted totals as you describe.

Hope this helps,

Cynthia