topic Re: Summarize on a noprint column in ODS and Base Reporting

Summarize on a noprint column

deleted_user — Wed, 05 Sep 2007 22:27:55 GMT

I want to order my report on a noprint column but can not get the compute after to automatically format without hardcoding the title and column totals myself. Is there an easier way to do this.

PROC REPORT DATA=RPT2
HEADLINE HEADSKIP LS=200
SPLIT='*';
TITLE;
DEFINE GROUP_TYPE / ORDER NOPRINT;
DEFINE ACCT / ORDER 'ACCT';
DEFINE SEG_SDES / 'DESCRIPTION';
DEFINE FGCA / 'ITC*FGC-FED' FORMAT=COMMA14.;
DEFINE LTOTAL / 'ITC*PLNT-FORCED' FORMAT=COMMA14.;
DEFINE NTOTAL / 'ITC*NATL-FORCED' FORMAT=COMMA14.;
DEFINE LNTOTAL / 'ITC*TOTAL' FORMAT=COMMA14.;
DEFINE PD_ACT / 'OGN*TOTAL' FORMAT=COMMA14.;
DEFINE DELTA / 'ITC * OVER/(UNDER) * OGN' FORMAT=COMMA14.;

COLUMN GROUP_TYPE ACCT SEG_SDES FGCA
LTOTAL NTOTAL LNTOTAL PD_ACT DELTA;

COMPUTE BEFORE _PAGE_;
LINE @02 'REPORT FIGJLITC FRITO-LAY, INC.
LINE @02 'JOBID ITCJLOGNW FIGJLITC REORT 1A: RECONCILE CO
VS OGN - CDSD';
LINE @49 'NATIONAL BY ACCOUNT';
LINE @49 "PERIOD &CURRPD, &CURRYR"
LINE @3 ' ' ;
LINE @3 ' ' ;
ENDCOMP;

COMPUTE AFTER GROUP_TYPE;
FGCA_TOTAL=FGCA.SUM;
LTOTAL_TOTAL=LTOTAL.SUM;
NTOTAL_TOTAL=NTOTAL.SUM;
LNTOTAL_TOTAL=LNTOTAL.SUM;
DELTA_TOTAL=DELTA.SUM;
OGN_TOTAL=PD_ACT.SUM;
TEST1 = 'TOTAL ' ||GROUP_TYPE||' '
||PUT(FGCA_TOTAL, COMMA14.)||' '
||PUT(LTOTAL_TOTAL, COMMA10.)|| ' '
||PUT(NTOTAL_TOTAL, COMMA10.)|| ' '
||PUT(LNTOTAL_TOTAL, COMMA14.)|| ' '
||PUT(OGN_TOTAL, COMMA14.)|| ' '
||PUT(DELTA_TOTAL, COMMA10.) ;
LINE @11 TEST1 $150.;
LINE ' ';
ENDCOMP;

Re: Summarize on a noprint column

Cynthia_sas — Thu, 06 Sep 2007 00:07:18 GMT

Hi:
Haven't really done much with this, but having been an English major in a former life, I proof subconsciously & noticed these:
[pre]
LINE @02 'REPORT FIGJLITC FRITO-LAY, INC. <--- missing single quote
and missing semi-colon

LINE @49 "PERIOD &CURRPD, &CURRYR" <--- missing semi-colon

[/pre]

Also a question -- what is your ultimate destination of choice -- LISTING window, HTML, RTF or PDF???

cynthia

Re: Summarize on a noprint column

Cynthia_sas — Thu, 06 Sep 2007 01:18:05 GMT

Hi... I'm not exactly sure what you mean by "automatically" format, but I find myself wondering why you don't use the BREAK statement. For example, this report on a subset of SASHELP.PRDSALE:
[pre]
REPORT FIGJLITC FRITO-LAY, INC.
JOBID ITCJLOGNW FIGJLITC REORT 1A: RECONCILE COVS OGN - CDSD
NATIONAL BY ACCOUNT
PERIOD Quarter 2, 2007

ITC ITC
REGION DESCRIPTION PLNT-FORCED NATL-FORCED
-----------------------------------------------------------

EAST FURNITURE 996 977
FURNITURE 959 819
FURNITURE 962 323
OFFICE 957 885
OFFICE 993 713
OFFICE 961 971
OFFICE 951 811
OFFICE 996 50
OFFICE 963 839
OFFICE 994 388
OFFICE 978 296
OFFICE 1,000 598
OFFICE 956 511
OFFICE 988 398
OFFICE 953 328
OFFICE 988 967
WEST FURNITURE 982 739
FURNITURE 954 201
FURNITURE 992 287
FURNITURE 996 643
OFFICE 981 268
OFFICE 966 522
OFFICE 986 942
OFFICE 991 143
OFFICE 952 855
OFFICE 983 180
========== ============== ==============
GERMANY 25,378 14,654
========== ============== ==============

EAST OFFICE 956 579
OFFICE 964 202
OFFICE 968 557
OFFICE 984 65
OFFICE 953 882
OFFICE 977 669
OFFICE 977 331
WEST FURNITURE 961 321
FURNITURE 984 451
FURNITURE 974 760
OFFICE 972 39
OFFICE 955 883
OFFICE 955 442
OFFICE 960 391
========== ============== ==============
U.S.A. 13,540 6,572
========== ============== ==============
[/pre]

was produced with this code -- note the use of the BREAK statement and the COMPUTE after statement.
[pre]
proc sort data=sashelp.prdsale out=prdsale;
where actual gt 950 and
country ne 'CANADA';
by country region prodtype;
run;

options nodate nonumber;
ods listing;
ods html file='c:\temp\autoformat.html' style=sasweb;

PROC REPORT DATA=prdsale nowd HEADLINE HEADSKIP LS=100 ps=60 SPLIT='*' nocenter;
TITLE;

COLUMN country region prodtype ACTUAL PREDICT;

DEFINE country / order NOPRINT;
DEFINE region / ORDER 'REGION';
DEFINE prodtype /display 'DESCRIPTION' f=$15.;
DEFINE ACTUAL / sum 'ITC*PLNT-FORCED' FORMAT=COMMA14.;
DEFINE PREDICT /sum 'ITC*NATL-FORCED' FORMAT=COMMA14.;

COMPUTE BEFORE _PAGE_;
LINE @02 'REPORT FIGJLITC FRITO-LAY, INC.';
LINE @02 'JOBID ITCJLOGNW FIGJLITC REORT 1A: RECONCILE COVS OGN - CDSD';
LINE @49 'NATIONAL BY ACCOUNT';
LINE @49 "PERIOD &CURRPD, &CURRYR" ;
LINE @3 ' ' ;
LINE @3 ' ' ;
ENDCOMP;

compute before country;
holdcntry = country;
endcomp;

break after country /summarize skip dol dul;

compute after country;
region = trim(holdcntry);
endcomp;

run;
ods html close;
[/pre]

And I also wonder why you're using a COMPUTE block:
[pre]
COMPUTE BEFORE _PAGE_;
LINE @02 'REPORT FIGJLITC FRITO-LAY, INC.';
LINE @02 'JOBID ITCJLOGNW FIGJLITC REORT 1A: RECONCILE COVS OGN - CDSD';
LINE @49 'NATIONAL BY ACCOUNT';
LINE @49 "PERIOD &CURRPD, &CURRYR" ;
LINE @3 ' ' ;
LINE @3 ' ' ;
ENDCOMP;
[/pre]

versus a SAS TITLE statement:
[pre]
title 'REPORT FIGJLITC FRITO-LAY, INC.';
title2 'JOBID ITCJLOGNW FIGJLITC REORT 1A: RECONCILE COVS OGN - CDSD';
title3 'NATIONAL BY ACCOUNT';
title4 "PERIOD &CURRPD, &CURRYR" ;
title5 ' ' ;
title6 ' ' ;
[/pre]

The compute block will put your text within the "box" of the table, but the title statement will put this text outside of the "box" of the table and will center the information at the top of the output page. Even though you are using many PROC REPORT options that are specific to the LISTING destination, I have included ODS HTML statements so you can compare how this looks in HTML versus the LISTING window.

cynthia

Re: Summarize on a noprint column

deleted_user — Thu, 06 Sep 2007 15:38:36 GMT

thanks for the help, it worked just like i wanted it too.