ods proc report with missing levels

[lovedieer]

Many thanks for reading my questions first.

I was asked to do series PDF reports for the oil production for different treatment level. Each region will have one report with all the data collected. 

we have about 300 regions with 5 major oil crops (i list corn and soybean in demo), for each crop, there are different brand, for example, soybean brand A, B. each brand will be applied 1 treatment level and multiple traits were measure cross years. i need to create a table for each treatment level for each brand. each page will contain 3 tables,  and for different crop i have to start a new page no matter the  previous page is full or only one table. have to follow the order of treatment level 1, 2, 3...12

The problem here is some regions do not have all the treatment level (level 1-12), and some regions do not have all the brands (brand A-D).

The way I did first was using ODS layout x= y= to define the location for each table, titles, and the page number, but the problem is i will have a  blank if the treatment level is null or brand is null, but if i use proc report by statement and startpage=no, i lost the control of the tables in each page and the page number. Also have problems with the titles . the real template is much more complicated, but i just have no clue about how to handle these null levels.

i have attached the dummy data and dummy template.

any help will be very very appreciated.

[lovedieer]

i think the sashelp.shoes data in SAS have the same pattern with my data.

the region is my region,

the subsidiary is my croptype

and the product is my treatment level;

[Cynthia_sas · Accepted solution]

Hi:

  What code have you tried? Are you using PROC REPORT or PROC TABULATE? From your original posting, you say that you have tried ODS LAYOUT and you have tried PROC REPORT? But you did not post any useful data or useful code. You say that SASHELP.SHOES has your structure, but you only list 3 variables in your posting; but, in your BOOK1 document you have MORE variables: REGION, CROP, BRAND, TREATMENT, TRAITMEASURED, YR2009, YR2010, YR2011, YR2012 and YR2013. So SASHELP.SHOES does NOT have nearly that many variables and it only has SALES, INVENTORY and RETURNS (3 numeric variables), instead of your YEAR variables. In addition, it looks like your data is pre-summarized. You seem to have a set of 4 rows for every TRAITMEASURED, including an Overall row, and SASHELP.SHOES is NOT pre-summarized and does NOT have any "overall" row for each group. So, SASHELP.SHOES 1) doesn't have enough variables to do a report and 2) isn't in the same structure (pre-summarized) as your data.

  For your problem of "null levels", you said: "The problem here is some regions do not have all the treatment level (level 1-12), and some regions do not have all the brands (brand A-D)." Can be handled in both PROC REPORT and TABULATE with the use of PRELOADFMT to ensure that you get categories for A, B, C, D, etc, even if they don't appear in the table. Although, it doesn't make sense to take up space with essentially empty tables. But since you did not show your code, it is not clear whether you are using PRELOADFMT or not. Since the solution will be specific to YOUR data (not SASHELP.SHOES) and the structure of YOUR data (not SASHELP.SHOES), it is hard to make any constructive comments in the absence of code.

  You might be better off working with Tech Support, where they can look at your REAL data and all of your code attempts to help you come to a good resolution.

cynthia

[lovedieer]

I think i make the question complicated with my world and dummy data attachment, all of the variables in my attachment are for display only, no summary calculation.  The overalleval was not a summary data, it is a trait measurement like oil product.  so there is no summary data in table. the year 2009-2013 etc are just for display purpose, it can be one year, two years, so it doesn't matter how many columns.

  let me make the question easier with the shoe table. and use Africa as example. I want to create a shoe sale report for Africa. Same subsidiary will have a one common title1, each shoe product will have an individual table and individual title2. (i know it looks silly with this 1 row table, but it represent my problem well)

i want to create a table like below for all shoe product. the ideal report template is in attached for shoes table.

therefore, for subsidiary Addis Ababa, i  will have 8 similar shoe product tables , each page can have maximum 3 tables, title 1 only appeared once at the first table for the same subsidiary, and title 2 need to incluced for each individual  product table.

                                                  title1 : Addis Ababa

                                                    title 2 Boot Sale

Prodcut: Boot	Number of stores	Total sales	Total Inventory	Total Returns
Boot	12	$29,761	$191,821	$769

therefore, it will occupie 3 pages for the subsidiary Addis Ababa,  and then i need start at page 4 for a new Subsidiary Algiers. ALgiers do not have all 8 product, only  7 products, therefore 7 table, still occupie 3 pages, for the next subsidiary Cairo, i will start at page7. so far so good, but when come to the subsidiary Johannesburg, there are only 5 shoe products, it will only use two pages, then i need go to a new subsidiary.  something like that (see attached). I am not sure whether i clear enough. i have attached a pdf layout. after all the subsidiary for Africa was done, then the report for Africa is done.

My original thought, hard code for all titles and all product one by one. I know it's looks stupid to do this, but that makes me have control on page number and table numbers in each page. but the problem is that  when I go to the second subsidiary, it has no product for women's causual, therefore, i have title text, and space reserved , but no data, and a big blank left in the report.

ods pdf file='test1.pdf';

ods layout start;

/*define page numer*/

ods region x=11in y=7.5in width=0.2 in  height=0.2in;

ods text= "1";

/*define text title*/

ods region x=2.0in y=0.5 in width=7.8 in  height=1in;

ods text= "Shoes sale for Addis Ababa";

ods region x=2.0in y=0.7 in width=7.8 in  height=1in;

ods text= "Boot sale";

ods region x=0.1in y=0.9in width=11.5in  height=3.5in;

proc report data=shoes nowd ;

column product Stores Sales inventory returns;

where region="Africa" and  subsidiary="Addis Ababa" and product="Boot";

run;

ods layout end;

ods pdf close;

This is really disgusting looking with many blanks for these missing shoe product.

so i am think using below code to avoid blank. but i lost the control of table number at each page, and the title not working very well.

ods pdf file='test1.pdf' startpage=no;

options nobyline nonumber;

title1 "Subsidiary: #byval(subsidiary)";

title2 "#byval(product) Sale";

proc report data=shoes nowd ;

column product Stores Sales inventory returns;

by subsidiary product;

where region="Africa" ;

define subsidiary/order noprint;

define product/order noprint;

compute before _page_;

  length pgline $25;

    pgline = catx(' ',"Product for",Product);

line pgline $25.;

endcomp;

run;

ods pdf close;

any help will really appreciated.