proc report: summarize across columns within another across variable

[ciro]

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

[ballardw]

---

@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.

[PaigeMiller]

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;

[ciro]

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.

[PaigeMiller]

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.

[ciro]

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 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;

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;

[Cynthia_sas]

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