topic SAS ODS Query in ODS and Base Reporting

SAS ODS Query

scb — Wed, 27 Sep 2017 03:05:46 GMT

May I know why the car2 report ‘Summary by model level’ not appear after the title, but start on the next page? Anyone can help? Thanks.

DATA CAR;
SET SASHELP.CARS;
RUN;

PROC SQL;
CREATE TABLE CAR1 AS SELECT DISTINCT
MAKE, SUM(INVOICE) AS INVOICE
FROM CAR WHERE MAKE EQ 'BMW'
GROUP BY MAKE
ORDER BY MAKE;
QUIT;

PROC SQL;
CREATE TABLE CAR2 AS SELECT DISTINCT
MAKE, MODEL, TYPE, ORIGIN, INVOICE
FROM CAR
ORDER BY MAKE, MODEL;
QUIT;

DATA CAR2;
SET CAR2 CAR2 CAR2 CAR2 CAR2;
RUN;

data _null_;
set CAR1 end=eof;
by MAKE;
/* On the first member of the BY-Group, create a new macro variable VARn */
/* and increment the counter FLAG. */
if first.MAKE then do;
flag+1;
call symput('var'||put(flag,8. -L),MAKE);
end;
/* On the last observation of the data set, create a macro variable to */
/* contain the final value of FLAG. */
if eof then call symput('tot',put(flag,8. -L));
run;

/* Create a macro to generate the new data sets. Dynamically produce data set names */
/* on the DATA statement, using subsetting criteria to create the new data sets */
/* based upon the value of the BY variable. */

%macro groups(dsn,byvar);
%do i=1 %to &tot;
data "_&&var&i";
set &dsn;
if &byvar="&&var&i" then output;
run;

proc sql noprint;
select distinct MAKE
into :orderedvars2 separated by ""
from CAR1
where MAKE eq "&&var&i";
quit;

options nodate nonumber center;
ods escapechar="^";
title;
footnote;

/* Create a data set containing the desired title text */
data test;
text="^nCar Maker Summary Report - &orderedvars2^n&MTHWORD &YYYY";
run;

%LET OUTPATH = D:\TEST\Incentive_ &YYYYMM-&orderedvars2..PDF;

ODS PDF FILE="&OUTPATH" compress=9 startpage=NO;

footnote1 j=c "Company Confidential";
*ods pdf text='^S={preimage="\\172.17.18.174\sas\_Common_\Images\saslogo.jpg"}';
ods pdf text="^20n";

/* Output the title text */
proc report data=test nowd noheader style(report)={rules=none frame=void}
style(column)={font_weight=bold font_size=20pt just=c};
run;

options nocenter;
options orientation=landscape papersize=A3;
*options orientation=portrait papersize=A3;
ods pdf startpage=now;
ods pdf style=analysis;

TITLE "Car Maker -&Mthword &YYYY Summary";

ODS TEXT = "BRAND OVERALL SALES";
PROC PRINT DATA=CAR1(WHERE=(MAKE = "&&var&i")) NOOBS LABEL;
LABEL MAKE="MODEL"
INVOICE="PRICE";
RUN;

ODS TEXT = " ";
ODS TEXT = " ";
ODS TEXT = "SUMMARY BY MODEL LEVEL";

PROC REPORT DATA=CAR2(WHERE=(MAKE = "&&var&i"))style(header)=[background=tan] NOWINDOWS HEADLINE HEADSKIP;
COLUMNS MAKE MODEL TYPE ORIGIN INVOICE;
DEFINE MAKE /GROUP 'Car Brand';
DEFINE MODEL / DISPLAY 'Car Model';
DEFINE TYPE / DISPLAY 'Type';
DEFINE ORIGIN / DISPLAY 'Origin';
DEFINE INVOICE / SUM 'Price' FORMAT=DOLLAR16.2;
COMPUTE BEFORE MAKE;

ENDCOMP;

RBREAK AFTER/ SUMMARIZE;

COMPUTE AFTER;
MAKE = "TOTAL";
ENDCOMP;

BREAK AFTER MAKE / SKIP SUMMARIZE DOL DUL;

RUN;

ODS PDF CLOSE;
ODS LISTING;
ODS LISTING CLOSE;

%end;
%mend groups;

options mprint symbolgen;
%groups(CAR1,MAKE)

Re: SAS ODS Query

ChrisNZ — Wed, 27 Sep 2017 05:23:58 GMT

To increase your chances of being helped:

1- Reduce your problem

No one is keen to run and analyse 100+ lines of badly formatted code that contains smileys.

Your problen can probably be replicated in a couple dozen lines at most. That part of the work is yours.

2- Format you code properly to make it legible. This is a habit that will help you too.

Indentations are useful, align things.

My personal preference is keywords in lower case and user words (variable names, etc) in upper case.
Find something that you like and stick to it.

3- Post code using the {i} or the "notepad" icon above.

Which of the following 2 steps is easier to read and debug?


PROC REPORT DATA=CAR2(WHERE=(MAKE = "&&var&i"))style(header)=[background=tan] NOWINDOWS HEADLINE HEADSKIP;
COLUMNS MAKE MODEL TYPE ORIGIN INVOICE;
DEFINE MAKE /GROUP 'Car Brand';
DEFINE MODEL / DISPLAY 'Car Model';
DEFINE TYPE / DISPLAY 'Type';
DEFINE ORIGIN / DISPLAY 'Origin';
DEFINE INVOICE / SUM 'Price' FORMAT=DOLLAR16.2;
COMPUTE BEFORE MAKE;
ENDCOMP;
RBREAK AFTER/ SUMMARIZE;
COMPUTE AFTER;
MAKE = "TOTAL";
ENDCOMP;
BREAK AFTER MAKE / SKIP SUMMARIZE DOL DUL;
RUN;

proc report data=CAR2(where=(MAKE = "&&var&i")) 
            style(header)=[background=tan] 
            nowindows 
            headline 
            headskip;
  columns MAKE MODEL TYPE ORIGIN INVOICE;
  define MAKE    / group   'Car Brand' ;
  define MODEL   / display 'Car Model' ;
  define TYPE    / display 'Type'      ;
  define ORIGIN  / display 'Origin'    ;
  define INVOICE / sum     'Price'     format=dollar16.2;
  rbreak after / summarize;
  compute after;
    MAKE = "TOTAL";
  endcomp;
  break after MAKE / skip summarize dol dul;
run;

Re: SAS ODS Query

ballardw — Wed, 27 Sep 2017 15:09:22 GMT

ODS Text creates a table of output and so with it following your proc print it comes after the print output. As a separate table it is controlled by the startpage setting in effect.

If you want that text to follow the title before the proc print then use a title2 or title3 or title4 statement (before the run for proc print).

Then clear it after the proc with

title2;