Report generation

[sid1234]

Hi,

Basically after running the program the header ends up on a different level compared to the probability level. The tables show up as two seperate tables due to the fact that they are two different datasets.

1; Is there a way to append the two data sets to reveal the results in two tables whilst on the same page.

2; The level of the Headers are all messed up. Would appreciate a simple solution. 

libname libcw '/folders/myshortcuts/Myfolders';

proc import datafile = '/folders/myshortcuts/Myfolders/OLYMPICS_DIGI.XLS'
DBMS= XLS
OUT= Libcw.OLYMPICS;
GETNAMES = YES;
RUN;

proc import datafile = '/folders/myshortcuts/Myfolders/OLYMPICS.XLS'
DBMS= XLS
OUT= libcw.OLYMPICS1;
GETNAMES = YES;
RUN;

DATA Descriptiona ;
Set libcw.OLYMPICS1 ;
Label Account_Name = Client Name
Opportunity_Owner = Sales Person(Champion)
Primary_Contact = Primary contact from Client Side
Created_Date = Deal creation in CRM Application
Probability____ = Probability assigned to different stages of the deal
Total_Property_s__Budget_Currenc = Currency
Total_Property_s__Budget = As of now money commited for the different slots
Comments = Comments
Stage = Current Stage of the deal
Sports_Elements_seleceted = Ad slot for the sports element
Opportunity_Name = CRM Application generated name for the deal
Description = Description of the deal
Deal_Comments = Comments from the sales person
Industry = Client serving industry
Last_Modified_Date = Latest of the deal modification in the CRM system
Total_Media_Value_Currency = Currency
Total_Media_Value = Estimated deal value;
Format Total_Media_Value dollar26.;
Run;

data Useful;
Set descriptiona;
keep Account_Name Opportunity_Owner Created_Date Probability____ Deal_Comments Total_Media_Value;
run;

data calculation1;
set useful;
format Tot_Forecast dollar26.;
Tot_Forecast = Total_Media_Value*Probability____/100;
run;

data calculation2;
set calculation1;
format Tot_Budget dollar26.;
Tot_Budget = sum(Total_Media_Value);
run;

data Olympicsq;
set libcw.olympics;
rename _probability___ = Probability____;
run;

data Useful1;
Set Olympicsq;
format Total_Media_Value dollar26.;
keep Account_Name Opportunity_Owner Created_Date Probability____ Deal_Comments Total_Media_Value;
run;

data calculation3;
set useful1;
Format Digital_Bugt dollar26.;
Digital_Bugt = Total_Media_Value;
run;

data calculation4;
set calculation3;
Format D_forecast dollar26.;
D_Forecast = Total_Media_Value*Probability____/100;
run;

PROC TABULATE DATA=calculation2 style = [color = black borderbottomcolor=black bordercolor=black borderleftcolor=black
borderrightcolor=black bordertopcolor=black
COLOR = BLACK borderwidth=2]
out = Summary1;
Title bold italic color = black 'London olympic pipeline as of 17th November 2010';
where Probability____>0;
CLASS Probability____ /descending style = [Backgroundcolor = Gray FontWeight = Bold color = black borderbottomcolor=black bordercolor=black borderleftcolor=black
borderrightcolor=black bordertopcolor=black
COLOR = BLACK borderwidth=2] ;
Var Tot_Budget Tot_Forecast/style= [backgroundcolor = gray color = black borderbottomcolor=black bordercolor=black borderleftcolor=black
borderrightcolor=black bordertopcolor=black
COLOR = BLACK borderwidth=2];
TABLES Probability____ = Probability all='', N = Nbr_of_Optys sum=''*Tot_Budget*f=dollar26. sum=''*Tot_Forecast*f=dollar26. /nocellmerge ;
Run;

PROC TABULATE DATA=calculation4 out = Summary2;
where Probability____>0;
CLASS Probability____ /descending ;
Var Digital_Bugt D_Forecast ;
TABLES Probability____ = Probability all='', N = Nbr_of_Optys sum='' *Digital_Bugt*f=dollar26. sum=''*D_Forecast*f=dollar26. ;
Run;

Best wishes,

Sid 

[sid1234]

here is the second file. Proc tabulate is the only option to get the desired output. 

[Cynthia_sas · Accepted solution]

Hi:
You have two (2) PROC TABULATE steps. Each TABULATE will create a separate table or set of tables. But, you do not show your ODS statements; however, if you are using PDF or RTF and assuming that both tables will fit on one page, then using STARTPAGE=NO on the ODS invocation statement will put both tables on 1 page. If you want the tables side-by-side, then there are other options -- both simple (COLUMNS=2) and COMPLEX (ODS LAYOUT) that will work for PDF. If you want tables side-by-side for HTML output, then using ODS HTMLPANEL is something to investigate.

TABULATE has a lot of ways to adjust the headers.

What do you need the OUT= for in the TABULATE steps (just curious) -- the OUT= files will not have the same structure as the TABULATE table.

When you say headers are not on the same level, I assume you are not liking the big empty box area in the upper left hand side of the table? There are ways around that depending on what you have in the row dimension.

Sorry, I don't open XLSX files on this machine. But In doing a mockup, I assume that you want the header for "PROBABILTY" to be on the same level as the column headers?? TABULATE doesn't do that by default.

If you change your TABLE statement to use BOX=, you can move the header for PROBABILITY up into the BOX area.

cynthia

here's some code to try that uses SASHELP datasets to illustrate the header part of your question:

** Original code using SASHELP data;
PROC TABULATE DATA=sashelp.shoes 
     style = [color = black borderbottomcolor=black bordercolor=black borderleftcolor=black
              borderrightcolor=black bordertopcolor=black COLOR = BLACK borderwidth=2];
 
Title bold italic color = black 'London olympic pipeline as of 17th November 2010';
where region in ('Asia' 'Pacific');
CLASS region /descending 
              style = [Backgroundcolor = Gray FontWeight = Bold color = black 
                       borderbottomcolor=black bordercolor=black borderleftcolor=black
                       borderrightcolor=black bordertopcolor=black COLOR = BLACK borderwidth=2] ;
Var sales inventory/
     style= [backgroundcolor = gray color = black borderbottomcolor=black bordercolor=black borderleftcolor=black
             borderrightcolor=black bordertopcolor=black COLOR = BLACK borderwidth=2];
TABLES region = 'Probability' all='', N = Nbr_of_Optys sum=''*sales*f=dollar26. sum=''*inventory*f=dollar26. /nocellmerge ;
Run;

PROC TABULATE DATA=sashelp.prdsale ;
CLASS country /descending ;
Var actual predict;
TABLES country = 'Probability' all='', N = Nbr_of_Optys sum='' *actual*f=dollar26. sum=''*predict*f=dollar26. ;
Run;


** alternate code showing BOX= option;
PROC TABULATE DATA=sashelp.shoes 
     style = [color = black borderbottomcolor=black bordercolor=black borderleftcolor=black
              borderrightcolor=black bordertopcolor=black COLOR = BLACK borderwidth=2];
 
Title bold italic color = black 'London olympic pipeline as of 17th November 2010';
where region in ('Asia' 'Pacific');
CLASS region /descending 
              style = [Backgroundcolor = Gray FontWeight = Bold color = black 
                       borderbottomcolor=black bordercolor=black borderleftcolor=black
                       borderrightcolor=black bordertopcolor=black COLOR = BLACK borderwidth=2] ;
Var sales inventory/
     style= [backgroundcolor = gray color = black borderbottomcolor=black bordercolor=black borderleftcolor=black
             borderrightcolor=black bordertopcolor=black COLOR = BLACK borderwidth=2];
TABLES region=' ' all='',
       N = Nbr_of_Optys sum=''*sales*f=dollar26. sum=''*inventory*f=dollar26. 
       /nocellmerge box='PROBABILITY';
Run;

PROC TABULATE DATA=sashelp.prdsale ;
CLASS country /descending ;
Var actual predict;
TABLES country = ' ' all='', 
       N = Nbr_of_Optys sum='' *actual*f=dollar26. sum=''*predict*f=dollar26. 
       / box='PROBABILITY';
Run;

[sid1234]

Hey.
had used the output function to individually broadcast the report. Also, an attempt at merging the two summary datasets and checking if the tables could be produced by using another tabulate statement to deliver two separate tables. In terms of the header I've managed to unmerge a cell however my main goal is to push the other three variables down to the level of probability. primarily to eliminate the extra cells. Still figuring out the correct ODS. Will share any doubts if they arise.
Thank you for the tips.
Best wishes,
Sid

[Cynthia_sas]

Hi: I'm not sure what you mean by "push the other three variables down. That is not typically something you can do with TABULATE. The big box in the upper left of TABULATE output separates the ROW area from the COLUMN area. The box is on the same level as the column headers. You can move the row header UP (in your case, Probability), but you typically can't move the column headers DOWN. Did you run my code? My code that uses the BOX= option shows all the headers on the same level. Here's my output using SASHELP data.
cynthia