BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
HelenLondon
Fluorite | Level 6

Hi,

I have a report created from proc report and would like to rename the lowest level nodes. Please see the example coding below:

title;

footnote;

options nodate nonumber center;

ods listing close;

ods document name=work.TOCtest(write);

ods escapechar='^';

ods pdf file="c:\temp\test.pdf"   style=minimal;

ODS PROCLABEL "PRD Sale Report";

proc sort data=sashelp.prdsale out=prdsale;

  by Country;

run;

data report;

  set prdsale nobs=last;

  by country;

  country=compress(country,'.');

  length sitestr $1000;

  retain sitestr ' ';

  if first.country then if sitestr=' ' then sitestr=compress(country); else sitestr=trim(sitestr)||' '||compress(country);

  if last then do;

    call symput('lastpage',compress(country));

  call symput('sitestr',trim(sitestr));

  end;

run;

%let sitett=%sysfunc(countw("&sitestr",' '));

%put lastpage=&lastpage sitestr=&sitestr sitett=&sitett;

proc REPORT data=REPORT nowindows headline contents="Form Level - details"

style(report)=[frame=void rules=none cellspacing=0 padding=3pt]

style(header)=[bordertopcolor=black bordertopwidth=1

               borderbottomcolor=black borderbottomwidth=1

               background=_udef_ font=(Arial, 8pt) textalign=l ]

style(column)=[font=(Arial, 8pt)];

   *by country;

   column country REGION DIVISION PRODTYPE PRODUCT QUARTER YEAR MONTH ACTUAL;

   define country /group ;

   define REGION /group ;

   define QUARTER /ORDER ;

   define YEAR/ORDER ;

   define MONTH/display;

   define ACTUAL /sum  format=dollar16.2;

   break after REGION/summarize skip;

   compute after REGION/style={bordertopcolor=black bordertopwidth=1};

    line ' ' ;

   endcomp;

   compute after country;

     if country="&lastpage" then myval='x';

   endcomp;

   break after country / summarize page;

   compute after _page_/ style={bordertopcolor=black bordertopwidth=1};

     length text $100;

     if myval='x' then text='^S={font_face=arial font_size=1pt}';

  else text='^S={just=r font_face=arial font_size=8pt} (Continued)';

  line text $100.;

   endcomp;

   rbreak after / summarize;

   compute after / style={font_size=10pt};

        line "total ";

   endcomp;

run;

ods document close;

ods pdf close;

proc document name=work.TOCtest;

list / levels=all;

run;

quit;

ods listing;

%macro test;

proc document name=work.tocnew(write);

   make \testpath;

   dir \testpath#1;

   setlabel \testpath#1 "testing-Form Level";

   copy \Report#1\Report#1\Report#1 to ^;

   %do i=1 %to &sitett;

   %let site=%scan(&sitestr,&i);

   %put site=&site;

   setlabel \Report#1\Report#1\Report#1\Report#&i. "Country=&site.";

   %end;

   run;

quit;

%mend;

%test;

ods pdf file='c:\temp\replay_new.pdf';

proc document name=work.tocnew;

   replay;

run;

quit;

ods _all_ close;

Right now the TOC has the lowest nodes: 'Table 1','Table 2','Table 3' and 'Table 4'. What I would like to be are: 'Canada','Germany','USA' and 'Total'. I thought I can use PROC document 'setlabel ' to rename it (see the above codes), but it gives the error: ERROR: Invalid document path: \Work.Tocnew\Report#1\Report#1\Report#1.

Also another 2 questions: 1 how to repeat group/order value on the first line of next page? and 2: how to add a line at the end of each page when the table continuing to next page?

Anyone can help me with this?  Thanks in advance!

Helen

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

  Hi: I'm posting this as a separate response to avoid making the other post too long.

  There is a code example in the following screen shot. Basically, it is a program with 3 PROC REPORTS, one for each COUNTRY value. The lowest level node in the PROC REPORT output is given the name Country=Canada, Country=Germany and Country=USA. The screen shot (example_diff_report_code_make_doc.png) only shows 1 example of the REPORT code. But you can see I make an ODS DOCUMENT store from that first run. At the end of the step is the code to see what's in the ODS DOCUMENT store.

  Next, the screen shot make_doc_from_3_report_steps.png shows what the document store looks like compared to the PDF Table of Contents and the ODS DOCUMENT store. The document window has arrows pointing to the listing of the document from PROC DOCUMENT and the PDF is superimposed in the pix as well, so you can see the lowest level node label is NOT reflected in the document store, but it IS known and WILL be used when you rearrange.

  Here's the code I used to do the rearranging. You can see in the previous screen shot, where the document object numbers change. I just hard-coded the values, but you could macro-ize this part, too.

proc document name=work.tocnew(write);

   make \testpath;

   dir \testpath#1;

   setlabel \testpath#1 "testing-Form Level";

   dir;

   dir \testpath#1;

   copy \work.diff_orig\Report#1\Report#1\Report#1 to ^;

   copy \work.diff_orig\Report#2\Report#1\Report#1 to ^;

   copy \work.diff_orig\Report#3\Report#1\Report#1 to ^;

  run;

quit;

 

ods listing;

proc document name=work.tocnew;

  list / levels=all;

run;

quit;

ods listing close;

  

ods pdf file='c:\temp\replay_new.pdf';

proc document name=work.tocnew;

   replay;

run;

quit;

ods _all_ close;

  I did not feel the need to over complicate the example with a macro program. Finally, the new ods document store work.tocnew is replayed to ODS PDF. You can see that in the final screen shot final_toc_after_ods_document.png, where you can see in the PDF that the desired node names are used AND you can also see that they are NOT evident in the list of what's in the DOCUMENT store.

  Here's a paper I wrote that dives into ODS DOCUMENT and PROC DOCUMENT in more detail that I really want to post in the forum space (it's over 20 pages): https://support.sas.com/resources/papers/sgf09/318-2009.pdf

 

   I don't think I would try to overcontrol the line thing and do my own page break processing I'd probably just use the JOURNAL style to get the line at the bottom of each page, instead. But this is my approach.

  I do see some wonkiness with SPANROWS, when there are 2 break variables. And, it does look like SPANROWS is not being entirely consistent. I think that's a valid item to bring up with Tech Support. They can involve the developers and ask if the behavior was by design or is something that needs to be addressed.

cynthia


example_diff_report_code_make_doc.pngwonky_break_spanrows_behavior.pngmake_doc_from_3_report_steps.pngfinal_toc_after_ods_document.pngspanrows_default_behavior_no_BREAK.png

View solution in original post

6 REPLIES 6
Cynthia_sas
SAS Super FREQ

Hi:

  Before you plunge into PROC DOCUMENT, how about addressing some of the other issues:

1) are you seeing this message in the log? Are you creating a GROUP (summary) report or an ORDER (detail) report?

NOTE: Groups are not created because the usage of DIVISION is DISPLAY. To avoid this note

change all GROUP variables to ORDER variables.

2)  Is the MYVAL='x' logic working for you?

cynthia

HelenLondon
Fluorite | Level 6

Hi Cynthia,

Thanks for pointing that. Now I changed the 'group' to 'order'. The output is still same as before. And myval='x' is working because I get new page at the end of every COUNTRY ('continued' after 'Cananda' and 'Germany' and  blank line  after 'USA' with lower black lines). Could you tell me how to rename those nodes and how to repeat those ordered values on the first line of new page and how to add a line at the end of page within each country? Thank you very much!

Helen

Cynthia_sas
SAS Super FREQ


Hi:

  You have a lot of questions. I believe that, as these Tech Support notes suggest, using SPANROWS will handle your issue of GROUP/ORDER values repeating on the top of the next page: http://support.sas.com/kb/7/887.html and http://support.sas.com/kb/39/594.html.

  The JOURNAL style generally does what you want with a line at the top and bottom of a TABLE. See the attached screen shot style_journal_line_bottom_page.png -- So if you had a table for each country, the JOURNAL style would give you a top line and a bottom line as shown for SUV, Mitsubishi pages. These pages also show the impact of SPANROWS. The code that produced this screen shot is here:

ods _all_ close;

title 'My Title';
footnote 'My Footnote';
ods pdf file='c:\temp\pageline_spanrows.pdf' style=journal;

ods proclabel 'Top Node';
proc report data=sashelp.cars nowd spanrows contents='Cars'
style(header)=[font=(Helvetica, 8pt) textalign=l ] 
style(column)=[font=(Helvetica, 8pt)];
  where type in ('Hybrid','SUV','Sports');
  column type make model msrp mpg_city;
  define type / order;
  define make / order;
  define model / display;
  define msrp / sum 'Suggested Price';
  define mpg_city / sum 'MPG';
  break before type / contents='Type Node' page;
  break after type / summarize page;
run;
ods pdf close;


  BTW, I used Helvetica, instead of plain Arial because I have talked to the developers too many times and have heard the whole Arial vs Helvetica story too many times. If you want Arial in your PDF document, you have to use the ADOBE version of the Microsoft Arial font. I believe the name is something like Arial Unicode MS, that you have to use. See this note about graphs and using the Microsoft Arial instead of Arial Unicode MS 12492 - "Warning: Font Arial could not be used" issued when using TrueType fonts -- the fonts look very similar, there are some slight differences in the space between each letter. The story about Arial is an old one, and you can read about it here if you like: The Scourge of Arial – Notebook – Mark Simonson My tendency is to just use Helvetica for PDF or to use 'Arial Unicode MS' as the value for FONT=. In this case, I took the lazy one word font name.

  There are a couple of different ways to do your ODS DOCUMENT/PROC DOCUMENT. I think I know what is wrong with your original code. If you do a PROC DOCUMENT with the LIST statement, you will see that even though there are 4 nodes in the PDF table of contents, there is only 1 node in the ODS DOCUMENT store. So your Macro loop is not working. See the screen shot: ods_document_vs_pdf_toc.png , which shows the 4 nodes from your original code, but then shows only 1 node in the PROC DOCUMENT LIST results for the TOCTEST document store.

  I would probably take a different approach and use the break before technique, (as described here http://support.sas.com/kb/31/278.html ) and shown in my code above to name the lowest level node the way I want in the created results. I am on my way to a meeting now, but will try to post more about ODS DOCUMENT nodes later.

cynthia


style_journal_line_bottom_page.pngods_document_vs_pdf_toc.pngMicrosoft_Arial_vs_Arial_Unicode_MS.png
HelenLondon
Fluorite | Level 6

Thank you so much for your help! I've learned a lot.

I have played your above codes and it looks great! But I need two levels summaries for my real report. So I added 'break after make/sumarize;'. Now on the page3 of pdf, TYPE value 'SUV' is not repeating anymore. The option 'SPANROWS' seems only working on MAKE now. Why is that?

Thanks again!

Also I am looking forward to learning how to rename those 'Type Node' created by 'break before' statement.

Helen

Cynthia_sas
SAS Super FREQ

  Hi: I'm posting this as a separate response to avoid making the other post too long.

  There is a code example in the following screen shot. Basically, it is a program with 3 PROC REPORTS, one for each COUNTRY value. The lowest level node in the PROC REPORT output is given the name Country=Canada, Country=Germany and Country=USA. The screen shot (example_diff_report_code_make_doc.png) only shows 1 example of the REPORT code. But you can see I make an ODS DOCUMENT store from that first run. At the end of the step is the code to see what's in the ODS DOCUMENT store.

  Next, the screen shot make_doc_from_3_report_steps.png shows what the document store looks like compared to the PDF Table of Contents and the ODS DOCUMENT store. The document window has arrows pointing to the listing of the document from PROC DOCUMENT and the PDF is superimposed in the pix as well, so you can see the lowest level node label is NOT reflected in the document store, but it IS known and WILL be used when you rearrange.

  Here's the code I used to do the rearranging. You can see in the previous screen shot, where the document object numbers change. I just hard-coded the values, but you could macro-ize this part, too.

proc document name=work.tocnew(write);

   make \testpath;

   dir \testpath#1;

   setlabel \testpath#1 "testing-Form Level";

   dir;

   dir \testpath#1;

   copy \work.diff_orig\Report#1\Report#1\Report#1 to ^;

   copy \work.diff_orig\Report#2\Report#1\Report#1 to ^;

   copy \work.diff_orig\Report#3\Report#1\Report#1 to ^;

  run;

quit;

 

ods listing;

proc document name=work.tocnew;

  list / levels=all;

run;

quit;

ods listing close;

  

ods pdf file='c:\temp\replay_new.pdf';

proc document name=work.tocnew;

   replay;

run;

quit;

ods _all_ close;

  I did not feel the need to over complicate the example with a macro program. Finally, the new ods document store work.tocnew is replayed to ODS PDF. You can see that in the final screen shot final_toc_after_ods_document.png, where you can see in the PDF that the desired node names are used AND you can also see that they are NOT evident in the list of what's in the DOCUMENT store.

  Here's a paper I wrote that dives into ODS DOCUMENT and PROC DOCUMENT in more detail that I really want to post in the forum space (it's over 20 pages): https://support.sas.com/resources/papers/sgf09/318-2009.pdf

 

   I don't think I would try to overcontrol the line thing and do my own page break processing I'd probably just use the JOURNAL style to get the line at the bottom of each page, instead. But this is my approach.

  I do see some wonkiness with SPANROWS, when there are 2 break variables. And, it does look like SPANROWS is not being entirely consistent. I think that's a valid item to bring up with Tech Support. They can involve the developers and ask if the behavior was by design or is something that needs to be addressed.

cynthia


example_diff_report_code_make_doc.pngwonky_break_spanrows_behavior.pngmake_doc_from_3_report_steps.pngfinal_toc_after_ods_document.pngspanrows_default_behavior_no_BREAK.png
HelenLondon
Fluorite | Level 6

Thank you so much, Cynthia!

I macro-ized your codes as below in case someone interested:

title;

footnote;

options nodate nonumber center;

proc sort data=sashelp.prdsale out=prdsale;

  by Country;

run;

data report;

  set prdsale nobs=last;

  by country;

  count=_n_;

  country=compress(country,'.');

  length sitestr $1000;

  retain sitestr ' ';

  if first.country then if sitestr=' ' then sitestr=compress(country); else sitestr=trim(sitestr)||' '||compress(country);

  if last then do;

    call symput('sitestr',trim(sitestr));

  end;

run;

%let sitett=%sysfunc(countw("&sitestr",' '));

%put sitestr=&sitestr sitett=&sitett;

ods _all_ close;

ods document name=work.TOCtest(write);

%macro old;

   %do i=1 %to &sitett;

   %let site=%scan(&sitestr,&i);

   %put site=&site;

ods proclabel '';

proc report data=report nowd spanrows contents="&site";

  where country="&site";

  column country region division PRODTYPE product quarter year month ACTUAL;

  define country / order;

  define region / order;

  define quarter / order;

  define year/ order;

  define month/display;

  define ACTUAL/sum format=dollar16.2;

  break before country/contents="Country=&site" page;

  break after country/summarize ;

  break after region / summarize;

run;

    %end;

%mend;

%old;

ods document close;

ods listing;

proc document name=work.TOCtest;

  list / levels=all;

run;

quit;

ods listing close;

ods pdf style=journal  file='c:\temp\replay_old.pdf' ;

proc document name=work.TOCtest;

   replay;

run;

quit;

ods _all_ close;

%macro new;

proc document name=work.tocnew(write);

   make \testpath;

   dir \testpath#1;

   setlabel \testpath#1 "testing-Form Level";

   dir;

   dir \testpath#1;

   %do i=1 %to &sitett;

   copy \work.TOCtest\Report#&i\Report#1\Report#1 to ^;

   %end;

  run;

quit;

ods listing;

proc document name=work.tocnew;

  list / levels=all;

run;

quit;

ods listing close;

%mend;

%new;

ods pdf style=journal file='c:\temp\replay_new.pdf';

proc document name=work.tocnew;

   replay;

run;

quit;

ods _all_ close;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2413 views
  • 3 likes
  • 2 in conversation