proc report

[SASPhile]

For my code:
proc report data=whole_v2 nowd NOWINDOWS HEADLINE MISSING;
COLUMN terr_id bsm physician_name total_scripts, year, productgroup;
define terr_id/group;
define bsm/group;
DEFINE physician_name / group 'PhyName' missing='(blank)';
DEFINE year / across;
DEFINE productgroup / across;
DEFINE total_scripts / sum format=comma9.;
break after terr_id/summarize;
run;
For the above code, the output in excel looks like this:
Terr_id bsm Phy_id
1011101 AAR ABC
DEF
GEH
IJK

1011101 -----------------total sum of the scripts---------------------------

But how would i genetate some thing like this:
Terr_id bsm Phy_id
1011101 AAR ABC
1011101 AAR DEF
1011101 AAR GEH
1011101 AAR IJK

1011101 (total) AAR 4,357,917 3,895,600
1. I'm able to get the summary of the total scripts.But how to get the entire row in bold. and total in paranthesis next to terr_id to get the summary of scripts.
2.if a phy_name is missing , i get a blank in my report. Rather how would i get it as "(blank)" with blank in paranthesis of the phy_name is missing.

[Cynthia_sas]

Hi:
I'm a bit confused. I see in your output that you show PHY_ID as being output, yet there's no PHY_ID in the PROC REPORT code. Your code also shows YEAR and PRODUCTGROUP in the code, as variables on the report, but in the output that you say you are getting in Excel, there is no evidence of YEAR or PRODUCTGROUP values.

It is surprising to me that you are even getting output in Excel. I would have expected the MISSING='(blank)' syntax to give you an error message, as it is invalid syntax for the MISSING otion on the DEFINE statement. MISSING is a single word option and does not use an =.

Some of your questions seem to point to STYLE overrides (how to get the summary row all bold) or COMPUTE blocks (also how to get the summary row bold and/or how to put (blank) into an empty report location and how to put the string (total) on the summary row). If you need COMPUTE blocks with ACROSS report items, you may need to read about using absolute column numbers for the COMPUTE block.

For more help with PROC REPORT, I suggest you look in the documentation for examples or look for user group papers about PROC REPORT or search for previous forum posting about creating reports using ACROSS variables.

Here are some previous forums postings that relate to your question:
http://support.sas.com/forums/thread.jspa?messageID=8805≥
http://support.sas.com/forums/thread.jspa?messageID=12406ぶ
http://support.sas.com/forums/thread.jspa?messageID=20110于
http://support.sas.com/forums/thread.jspa?messageID=18199䜗
http://support.sas.com/forums/thread.jspa?messageID=17341䎽

cynthia

[SASPhile]

Hi Cynthia,
I posted wrong code .
My question is:

I have year,productgroup and year_month used for grouping.
If I have to get summary of total scripts grouped by year,
I use the following:
proc report data=whole_v2 nowd NOWINDOWS HEADLINE MISSING;
COLUMN terr_id bsm physician_name total_scripts, year, productgroup;
define terr_id/group;
define bsm/group;
DEFINE physician_name / group 'PhyName';
DEFINE year / across;
DEFINE productgroup / across;
DEFINE total_scripts / sum format=comma12.;
break after terr_id/summarize skip ol style=[font_weight=bold background=red];
/* rbreak after terr_id / summarize skip ol style=[font_weight=bold background=red];*/
run;


The requirement is:
If I need the summary of total scripts for year and year_month in the same report?

Year will consolidate all the total scripts from 2007,2008,and till current month in 2009.
year_month should give us the summary for 2009-01,2009-02,2009-03 and the same for coming months data.

[Cynthia_sas]

Hi:
Again, you would just be introducing a new ACROSS variable into the COLUMN statement ... and, of course, you'd need a DEFINE statement for the ACROSS variable. So, let's say your variable was YR_MON...then the COLUMN statement would be:
[pre]
COLUMN terr_id bsm physician_name
total_scripts, year, YR_MON, productgroup;
define terr_id/group;
define bsm/group;
DEFINE physician_name / group 'PhyName';
DEFINE year / across;
DEFINE YR_MON / across;
DEFINE productgroup / across;
DEFINE total_scripts / sum format=comma12.;
[/pre]

However, calculating a total for each year and then the overall total would require using a COMPUTE block and require using Absolute column numbers, such as _C2_, _C3_, _C4_, etc, etc.

For an example of using Absolute column numbers refer to this previous forum posting
http://support.sas.com/forums/thread.jspa?messageID=20107事

or this paper:
http://support.sas.com/rnd/papers/sgf07/sgf2007-report.pdf

A different approach might be to try a PROC TABULATE approach, because TABULATE has a universal CLASS variable ALL, that allows you to get both totals and subtotals in virtually any table dimension. In addition, TABULATE has the MISSTEXT= option that allows you to put a longer text string into cells with missing values. The general form of a tabulate TABLE statement is:
[pre]
table row_dimension,
column_dimension / misstext='N/A';
[/pre]

cynthia

[SASPhile]

I tried this:
proc tabulate data =whole_v2;
class terr_id bsm region physician_name productgroup MD_zip year year_month;
var total_scripts;
table (terr_id bsm region physician_name productgroup MD_zip year year_month)* total_scripts;

run;

Hoping it would the sum of 2007,2008 and 2009 years for all total_scripts by grouping terr_id bsm region physician_name productgroup MD_zip year

And sum of 2007-01 to 2009-03 grouping on terr_id bsm region physician_name productgroup MD_zip year_month

But it was in vain!

[Cynthia_sas]

Hi:
In this example, you have EVERYTHING (all the variables) in the COLUMN dimension -- you don't have any of your CLASS variables going down the rows. In my opinion, that's going to be a very unacceptable table.

I'd expect something more like this (again -- just using your PROC REPORT variables in a reasonably equivalent table statement {CODE NOT TESTED}):
[pre]
class year yr_mon productgroup terr_id bsm Physician_name;
var total_scripts;
table terr_id*bsm*(Physician_name all='BSM Tot') all='Grand Total'*{style={font_weight=bold background=red}},
year=' '*(yr_mon=' ' all='Year Total')*productgroup=' '*total_scripts=' '*sum
all='Grand Total'*productgroup=' '*total_scripts=' '*sum /
format_precedence=row;
keylabel sum=' ';
format yr_mon monyy5.;

[/pre]

In this code, terr_id, bsm, physician_name, BSM total and a grand total all move to the ROW dimension. Then total_scripts for year, yr_mon, year total, productgroup, and Grand Total of total_scripts are in the COLUMN dimension. In addition, the total rows are going to be RED because of the STYLE=override and format_precedence=ROW setting.

It looks like you added some variables, I don't recall seeing MD_zip or region in your previous posts.

I like to think of PROC TABULATE as being the premier "slicer and dicer" of data, based on your class variables. I'm not sure what you mean when you say "Hoping it would sum 2007, 2008 and 2009 years for all total_scripts" since TABULATE will do just about any sum in any dimension you can think of.

Look at some of these TABULATE examples for more ideas.
http://www2.sas.com/proceedings/sugi25/25/iv/25p159.pdf
http://www.nesug.org/proceedings/nesug07/cc/cc07.pdf
http://www2.sas.com/proceedings/sugi30/258-30.pdf
http://www2.sas.com/proceedings/sugi24/Handson/p153-24.pdf
http://www.lexjansen.com/pnwsug/2004/ANYONECANLEARN.pdf

Generally, I find a table that I like and then in my mind do the substitution routine (where they have country, I want zip code; where they have region, I want division; where they have sales, I want total_scripts). I also find it helpful to sketch out a picture of the table I hope to produce because this helps me figure out how to build the table statement.

cynthia

[SASPhile]

Hi Cynthia,
Thanks a lot for helping with proc tabulate. I added md_zip to the class and table statement
thinking it would give me the summary at terrid,bsm mdzip, and physician level.Instead it gave me a different result.
Can you help me where I'm going wrong?

proc tabulate data=whole_v2;
class year year_month productgroup terr_id bsm md_zip Physician_name;
var total_scripts;
table terr_id*bsm*md_zip(Physician_name all='BSM Tot') all='Grand Total'*{style={font_weight=bold background=red}}, year=' '*(year_month=' ' all='Year Total')*productgroup=' '*total_scripts=' '*sum all='Grand Total'*productgroup=' '*total_scripts=' '*sum / format_precedence=row; keylabel sum=' ';
run;




The output should look like this:
Fieba and Novo are two categories in productgroup


terr_id bsm md_zip phy_name 2007,2008&2009 2007,2008&2009 2007-01 2009-03
Fieba total Novo total Fieba Novo Fieba Novo

1101 ABC 0123 Dr.Q 20,000 55,000 3,000 2,500 7,000 8,000
1101 ABC 3456 Dr.M 20,000 55,000 3,000 2,500 7,000 8,000
1101 ABC (blank)Dr.J 20,000 55,000 3,000 2,500 7,000 8,000

Thanks.

[Cynthia_sas]

Hi:
This is my approach with TABULATE. I start with a picture or sketch of the table I want to build. Starting with something simple, just a few variables before I move to more complex. Personally, I wouldn't have added more variables into the mix until I understood how tabulate was working and what effect various table operators had on the table structure.

Having Physician_name and their md_zip (zip code) in the same dimension doesn't make sense to me, because each Physician has his/her own unique zip code???? So if you cross or nest each physician within each md_zip, you'll be adding unnecesary levels to the ROW dimension? Of course, since I don't have a clear picture of what your INPUT data looks like, this is just a guess. I also don't understand what you're showing below -- is that INPUT data or is that the OUTPUT table that you want TABULATE to build????

At this point, my only suggestion for your TABULATE code is that it looks to me like you have a table operator missing between md_zip and Physician_name.
Proc tabulate with this syntax (as shown) probably would have nested terr_id*bsm*md_zip in the row dimension and then underneath those nested rows, you would have probably gotten a row for each physician, then a row for BSM Total (which would not have been the BSM Total) and then a Grand Total row. Putting an asterisk between md_zip and Physician_name would have done nesting, but then if the ALL was supposed to be for BSM total, it was probably in the wrong place.

Table operators are either * (for crossing or nesting) or space (for stacking). ALL just instructs TABULATE to give you summary statistics in whichever place, row or column you insert it into the TABLE statement --- and it can be inserted more than once. You will never get a summary (or ALL) calculated unless you put it in the correct place in the TABLE statement.

The only thing I can suggest is that you start very simply. Shown below is a program I use for my beginner student questions (with SASHELP.SHOES data) that illustrates some of the basic table operators, *, space and parens used with ALL in the different dimensions.

Otherwise, you may be better served by working with Tech Support, because they can look at your INPUT data and then, use YOUR data with the working code that you've already tried, to see if they can help you come up with a solution.

cynthia

TABULATE Examples of TABLE Operators and ALL:
[pre]
ods listing;

data shoes;
set sashelp.shoes;
where region in ('Asia', 'Canada') and
(product contains 'Dress' or
product contains 'Casual' or
product in ('Slipper', 'Sandal'));
if product =: 'Women' then type = '1) Women';
else if product =: 'Men' then type = '2) Men';
else type = '3) Both';
run;

proc print data=shoes(obs=75);
title 'What the INPUT data looks like';
var region type product sales;
run;

ods listing close;
ods html file='c:\temp\simple_tab.html' style=sasweb;
proc tabulate data=shoes;
title 'Showing Table Operators and All';
class type region product ;
var sales;
table region all='Region Total' product all='Product Total',
sales='Sales Total'*sum
/box='Row Stacked';

table region*(product all='Region Total') all='Grand Total',
sales='Sales Total'*sum
/box='Row Nested';

table region*(product all='Region Total') all='Grand Total',
type='Type'*sales=' '*sum all*sales=' '*sum
/box='One Var in COL dim' misstext='N/A';

** Next 2 generate same output with different table operators;
table region all='Grand Total',
type='Type'*sales=' '*sum all='Type Total'*sales=' '*sum
product='Product'*sales=' '*sum all='Product Total'*sales=' '*sum
/box='Col Stacked 1 Move Product to COL dim' misstext='N/A';

** This table also generates MEAN statistics;
table region all='Grand Total',
(type='Type' all='Type Total')*sales=' '*sum
(product='Product' all='Product Total')*sales=' '*sum
all='Region Average Sales'*sales=' '*mean
/box='Col and ALL inside parens with space operator and ALL' misstext='N/A';

table region all='Grand Total',
type='Type'*(product=' ' all='Type Total')*sales=' '*sum
all='Sales Total'*sales=' '*sum
/box='Product Nested under TYPE with ALL' misstext='N/A';

keylabel sum=' ' mean=' ';
run;

ods html close;
[/pre]