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?
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
Thanks.
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
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 |
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.