Problem with using percentages

[pasvorto]

I have a proc tabulate that needs to do percentages but not in the normal way. Here is the code I have now:

options orientation=landscape;

/* ods rtf file="progsummary4table_prots_all.rtf" style=MonochromePrinter; */

/* ods pdf file="progsummary4table_prots_all.pdf" notoc nocolor style=default fontscale=60 uniform startpage=yes;  */

ods html headtext="<STYLE> TD {MSO-NUMBER-FORMAT:\@}</STYLE>"

                   file="~/progsummary4table_prots_all.xls" style=default;

Title1 "Interventional Therapeutic and Other Interventional Clinical Protocols";

Title2 "Activated By Reporting Year";

Title3 "Reporting Period: &start_dt2 to &end_dt2";

title4;

proc tabulate data=allproter format=best8. order=data;

where rnd_loc in (195 196 197 2404 2456 2303 1953 231 230) and year_on_tab>=year(&start_dt.) and inter='Intervention';

   class rnd_loc sponsor2 year_on_tab;

   var mayot;

   table (sponsor2=' ' all='Total Accrual to Interventional Clinical Protocols') ,

     year_on_tab=' '*(mayot=' '*sum=' ' COLPCTN='% of Annual Total'*f=4.2)    <== note use of 'colpctn' to get column % figures

     all='Total'*mayot=' '*sum=' '/BOX='Reporting Year' misstext='0';

run;

This is an example of what it produces:

Interventional Therapeutic and Other Interventional Clinical Protocols
Activated By Reporting Year
Reporting Period: 01/01/2008 to 12/31/2012
Reporting Year	2008		2009		2010		2011		2012		Total
		% of Annual		% of Annual		% of Annual		% of Annual		% of Annual
		Total		Total		Total		Total		Total
National Group	92	40.9	45	36.9	37	38.5	33	34.7	26	26.3	233
Externally Peer-Reviewed	41	18.2	22	18.0	10	10.4	12	12.6	12	12.1	97
Institutional	41	18.2	8	6.56	14	14.6	11	11.6	14	14.1	88
Industrial	51	22.7	47	38.5	35	36.5	39	41.1	47	47.5	219
Total Accrual to Interventional Clinical Protocols	225	100	122	100	96	100	95	100	99	100	637

What I want to happen is for the % columns to go away. I can do that, but I need the numbers so they are here for now. Under the last row (Total Accrual...) I need it to say this:

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

% of Patients on Institutional Studies  |    18.2                            |  18.0                            | 10.4                           | 12.6                           | 12.1

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Any thoughts on how I can just isolate one row for doing the calculation and then posting at the bottom of the report?

[SteveDenham]

You may want to repost this to SAS Procedures or ODS and Base Reporting, as the participants there are much more active and are likely to be much more skilled in this area.

Steve Denham

[pasvorto]

Thanks.

[Cynthia_sas]

Hi:

Only PROC REPORT will let you "make" a column but then hide it and use the numbers at a break point. So I think you're going down the wrong road with TABULATE as an approach. But, to be able to help at all, it is necessary to understand your final desired results, but to also understand the structure and variables of the input data -- what are your variables that make categories, what are percents based on, etc.

  If you cannot post your data or your data is too big, can you make up some "fake" data that will simulate the structure of what you have (maybe just a couple of groups) and then show both the input data and the desired results.

cynthia

[pasvorto]

Here is some sample data:

study	dog	CASE	RND_LOC	mayot	year_on_tab	sponsor2	inter	type2
1	3325	CLL	246127	MC Rochester	1	2009	Externally Peer Rev.	Intervention	The
2	3750	CLL	220698	MC Arizona	1	2009	Externally Peer Rev.	Intervention	The
3	3814	CLL	194870	MC Rochester	1	2008	Externally Peer Rev.	Non-Intervention	CorAnc
4	3843	CLL	191704	MC Rochester	1	2008	National Other	Intervention	The
5	3889	CLL	196239	MC Rochester	1	2008	Externally Peer Rev.	Non-Intervention	CorAnc
6	4042	CLL	201700	MC Rochester	1	2008	Externally Peer Rev.	Intervention	The
7	4048	CLL	192925	MC Rochester	1	2008	Externally Peer Rev.	Non-Intervention	CorAnc
8	4079	CLL	191705	MC Rochester	1	2008	National Other	Non-Intervention	CorAnc
9	4288	CLL	199364	MC Rochester	1	2008	National NCCTG	Intervention	The
10	4604	CLL	268417	MC Arizona	1	2010	Institutional	Intervention	The
11	4626	CLL	201768	MC Rochester	1	2008	Institutional	Intervention	The
12	4635	CLL	218210	MC Arizona	1	2009	Institutional	Intervention	The
13	4679	CLL	249754	MC Rochester	1	2010	Institutional	Non-Intervention	CorAnc
14	4751	CLL	203323	MC Rochester	1	2008	Institutional	Non-Intervention	CorAnc
15	4791	CLL	252628	MC Arizona	1	2010	Institutional	Non-Intervention	CorAnc
16	4805	CLL	199366	MC Rochester	1	2008	National NCCTG	Non-Intervention	CorAnc
17	4870	CLL	244566	MC Arizona	1	2009	Industrial	Intervention	The
18	4955	CLL	205651	MC Rochester	1	2008	Institutional	Non-Intervention	CorAnc
19	4956	CLL	205652	MC Rochester	1	2008	Institutional	Non-Intervention	CorAnc
20	4957	CLL	205653	MC Rochester	1	2008	Institutional	Non-Intervention	CorAnc
21	4958	CLL	205654	MC Rochester	1	2008	Institutional	Non-Intervention	CorAnc
22	4959	CLL	205655	MC Rochester	1	2008	Institutional	Non-Intervention	CorAnc
23	4974	CLL	249756	MC Rochester	1	2010	Institutional	Non-Intervention	CorAnc
24	4975	CLL	205403	MC Rochester	1	2008	Institutional	Non-Intervention	CorAnc
25	5025	CLL	246483	MC Rochester	1	2009	Externally Peer Rev.	Intervention	The
26	5139	CLL	252631	MC Arizona	1	2010	Institutional	Non-Intervention	CorAnc
27	5140	CLL	268422	MC Arizona	1	2010	Institutional	Non-Intervention	CorAnc
28	5141	CLL	252633	MC Arizona	1	2010	Institutional	Non-Intervention	CorAnc
29	5142	CLL	268424	MC Arizona	1	2010	Institutional	Non-Intervention	CorAnc
30	5143	CLL	255378	MC Arizona	1	2010	Institutional	Non-Intervention	CorAnc
31	5144	CLL	262241	MC Arizona	1	2010	Institutional	Non-Intervention	CorAnc

[Cynthia_sas]

Hi: You show MAYOT as being character -- with values of "MC Arizona", "MC Rochester", etc. But in your code you define MAYOT in a VAR statement. Character variables cannot be used for analysis. Is your sample data correct? Also, you show year_on_tab in your sample data as being a year value, but I only see values of 1 in the year_on_tab column. Sponsor2, however, seems to be a year value. It almost looks like some of your data got "shifted". Also, you show values for rnd_loc in the WHERE statement that are not in the sample data, so I assume that your sample data would run or should run without the WHERE?

Can you clarify?

cynthia

[pasvorto]

Sorry for taking so long to replay. I got it to work. I just had to take lengthier measures that I thought I would. I appreciate all the responses.