How to create a 2x2 table with a third variable as the content of the table

[joachimg]

Hi all, I don't know if my title makes any sense, but here is the issue. I have a dataset with the variables year, cause of death, race/ethnicity, and ratio. I want to create a table that is sort of like a 2x2 table by cause, except instead of having frequencies in the table, I want the ratio variable to populate the table. Like, if I have five categories for cause of death, four years, and 5 categories of race/ethnicity, I want five tables (or one big table) that look like what would be generated from this code:

proc sort data=dataset; by cause; run;
proc freq data=dataset;
tables year*race;
by cause;
run;

Except I would see the ratios in the table, and be able to say (for example) that the mortality ratio for NH white participants in 2012 whose cause of death was suicide is 3.5 per 100,000 (totally made up number). Is this possible, either in SAS or through exporting into Excel? I'm not sure how clear this is so please let me know if I can clarify anything. Thank you. 

[sbxkoenk]

You need a cross tabulation table (or crosstab) and you can easily make that with PROC REPORT.

See :

Paper 388-2014

Sailing Over the ACROSS Hurdle in PROC REPORT

Cynthia L. Zender, SAS Institute Inc., Cary, NC

https://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf

BR, Koen

[Ksharp · Accepted solution]

If your final desired output is a Excel file, you could try to use PROC REPORT.

If you want sas dataset,that is another story.

data have;
call streaminit(123);
do cause='a','b','c';
 do year=2020 to 2025;
  do race='x','y','z';
    ratio=rand('uniform');
	output;
  end;
 end;
end;
run;

options nobyline;
ods _all_ close;
ods excel file='c:\temp\want.xlsx' options(embedded_titles='yes' sheet_name='RxC ratio table' sheet_interval='none');
title "Cause: #byval1";
proc report data=have nowd;
by cause;
columns year ratio,race;
define year/group;
define race/across;
define ratio/analysis '';
run;
ods excel close;

[joachimg]

THANK YOU!!!!!!