topic Re: compute after page. line ' ' to change dynamically in ODS and Base Reporting

compute after page. line ' ' to change dynamically

MART1 — Thu, 13 Aug 2020 14:37:59 GMT

Hello

I'm using the compute after _page_ function (found here ) to add blank spaces between tables.

This works brilliantly but I was wondering if I can get the code to add the number of lines based on the number of lines of each report (rather than giving a set number of lines).

Hopefully the picture below will explain it better.

I'd like to align the "Reports" to each "Charts", but the height of each Reports can vary, while the height of the charts is always the same.

(this is only dummy data; in the real datasets the variance is more evident).

This is the code

data CHART_TABLE;
	input Date	App$ Conn $ Records CV	Mean Median;
	datalines;

202002 APP_A CONN1 35 0.9 46 40
202003 APP_A CONN1 26 0.9 8 5
202004 APP_A CONN1 41 0.3 39 46
202002 APP_A CONN2 12 0.9 3 16 
202003 APP_A CONN2 40 0.7 16 5
202004 APP_A CONN2 49 0.9 28 4
202002 APP_B CONN1 21 0.3 0 5
202003 APP_B CONN1 25 0.1 47 28
202004 APP_B CONN1 19 0.6 33 1
202002 APP_B CONN2 47 01.60 4 9
202003 APP_B CONN2 16 0.12 20 28
202004 APP_B CONN2 35 0.5 0 6
202002 APP_B CONN2 42 0.3 12 22
202003 APP_B CONN2 8 0.1 37 41
202004 APP_B CONN2 9 0.7 42 3
202002 APP_B CONN4 30 0.5 1 22
202003 APP_B CONN4 15 0.2 45 26
202004 APP_B CONN4 45 0.8 17 47
202002 APP_C CONN3 55 0.9 46 45
202003 APP_C CONN3 24 0.9 9 5
202004 APP_C CONN3 40 0.4 31 42
202002 APP_C CONN5 33 0.5 51 20
202003 APP_C CONN5 20 0.8 7 8
202004 APP_C CONN5 40 0.5 31 86
run;

PROC SORT data=CHART_TABLE
	out=CHAR_TABLE_SORT;
	by  App Conn Date;




TITLE1 "Reports";

proc report data=WORK.CHAR_TABLE_SORT nowd;
by App;
	column  DATE  App Conn CV Mean Median Records;
	define DATE / group ;
	define App / group ;
	define Conn / group ;
	define CV / group;
	define Mean / group;
	define Median / group;
	define Records / sum;

  compute after _page_ / style={ BORDERCOLOR=white BORDERLEFTCOLOR=white};
    line ' ';
    line ' ';
    line ' ';
	line ' ';
    line ' ';
    line ' ';
    line ' ';
    line ' ';
    line ' ';
    line ' ';
    line ' ';
    line ' ';    
    line ' ';
    line ' ';
    line ' ';
    line ' ';
    line ' ';
    line ' ';
    line ' ';
    line ' ';

  endcomp;

run;

quit;

Also I cannot get rid of the right border!! despite having tried (I think) every possible option.

Many thanks

Re: compute after page. line ' ' to change dynamically

Cynthia_sas — Thu, 13 Aug 2020 16:58:11 GMT

Hi:

In your proc report statement you could try:

proc report data=...

style(report)={rules=none frame=void cellspacing=0};

but that might not work depending on your destination. The border looks like the border of the entire table. Unfortunately when you add a LINE statement to PROC REPORT, the empty line is inserted inside the boundary of the table.

Are you using Enterprise Guide? If so, what version of EG are you using? Is the ultimate destination HTML, RTF or PDF for your output. You might try ODS LAYOUT with ODS PDF or ODS HTML to see if you can get better results without using LINE statements for spacing and placement.

Cynthia

Re: compute after page. line ' ' to change dynamically

MART1 — Fri, 14 Aug 2020 09:26:04 GMT

thanks @Cynthia_sas for your reply

just tried

style(report)={rules=none frame=void cellspacing=0

but it does not change the placement of the tables; I'll keep playing around (maybe as you mentioned my version is too old?).

I'm using EG 7.13, and the ultimate destination is HTML.

I never used ODS HTML before but I see how it might fix quite a few of my problems!!

thanks

Re: compute after page. line ' ' to change dynamically

Cynthia_sas — Fri, 14 Aug 2020 14:57:04 GMT

Hi:
It just occurred to me that you might be generating reports from multiple outputs using the Report Layout tool in Enterprise Guide. If that is the case, then you will not see any border style changes reflected there. EG's report layout tool is manipulating the objects after they have been created, I'm not sure you can add anything to the style specification that will impact EG.
Cynthia

Re: compute after page. line ' ' to change dynamically

MART1 — Fri, 21 Aug 2020 10:56:04 GMT

Thanks Cynthia.

Yes I'm using the Report Layout tool combining different outputs. Had a try using ODS HTHM but had no luck.

Unfortunately I think I'll have to use Excel as a "middle man" for this.

thanks for your help anyway

Re: compute after page. line ' ' to change dynamically

Cynthia_sas — Fri, 21 Aug 2020 14:46:57 GMT

Hi:
You have to use ODS HTML with ODS LAYOUT. ODS HTML won't do side by side output. Take a look at the example on page 24 of this paper: https://support.sas.com/resources/papers/proceedings16/SAS5762-2016.pdf -- it's not exactly what you want because I have put the graphs side by side instead of a graph and a table. But some modification of the procedures being used should allow you to do a graph on the left and a table on the right. Here's an example using SASHELP.CLASS.


ods html(id=1) path='c:\temp' (url=none)
        gpath='c:\temp' (url=none)
        file="show_layout.html" ;

title font=Helvetica bold h=14pt 'Using PROC REPORT and ODS LAYOUT';

ods layout gridded rows=2 columns=2 column_widths=(3.75in 3.75in);

ods region;
ods graphics / height=3in width=3.5in;
proc sgplot data=sashelp.class noautolegend;
title 'Preteens';
  where age le 13;
  vbar sex / response=height stat=mean datalabel group=sex ;
run;
title;


ods region;
ods graphics / height=3in width=3.5in;
proc report data=sashelp.class;
  where age le 13;
  column age sex height,(min mean median max);
  define age / group;
  define sex / group;
  define min / f=7.2;
  define mean / f=7.2;
  define median / f=7.2;
  define max / f=7.2;
  rbreak after / summarize;
run;


ods region;
ods graphics / height=3in width=3.5in;
proc sgplot data=sashelp.class noautolegend;
title 'Teens';
  where age gt 13;
  vbar sex / response=height stat=mean datalabel group=sex ;
run;
title;

ods region;
ods graphics / height=3in width=3.5in;
proc report data=sashelp.class;
  where age gt 13;
  column age sex height,(min mean median max);
  define age / group;
  define sex / group;
  define min / f=7.2;
  define mean / f=7.2;
  define median / f=7.2;
  define max / f=7.2;
  rbreak after / summarize;
run;

ods layout end;

ods html(id=1) close;

Cynthia

Re: compute after page. line ' ' to change dynamically

MART1 — Wed, 02 Sep 2020 13:02:48 GMT

Thanks @Cynthia_sas. That would work brilliantly if I had individual charts and reports, however I'm splitting both by using BY.

Using ODS LAYOUT, this is what get (using line ' ')

the screenshot only shows the top of the output, but as you scroll further down the misalignment between chart and table is more apparent.

This is the code I'm using; amending the height of the ODS LAYOUT does not improve it

data CHART_TABLE;
	input Date	App$ Conn $ Records CV	Mean Median;
	datalines;

202002 APP_A CONN1 35 0.9 46 40
202003 APP_A CONN1 26 0.9 8 5
202004 APP_A CONN1 41 0.3 39 46
202002 APP_A CONN2 12 0.9 3 16 
202003 APP_A CONN2 40 0.7 16 5
202004 APP_A CONN2 49 0.9 28 4
202002 APP_B CONN1 21 0.3 0 5
202003 APP_B CONN1 25 0.1 47 28
202004 APP_B CONN1 19 0.6 33 1
202002 APP_B CONN2 47 01.60 4 9
202003 APP_B CONN2 16 0.12 20 28
202004 APP_B CONN2 35 0.5 0 6
202002 APP_B CONN2 42 0.3 12 22
202003 APP_B CONN2 8 0.1 37 41
202004 APP_B CONN2 9 0.7 42 3
202002 APP_B CONN4 30 0.5 1 22
202003 APP_B CONN4 15 0.2 45 26
202004 APP_B CONN4 45 0.8 17 47
202002 APP_C CONN3 55 0.9 46 45
202003 APP_C CONN3 24 0.9 9 5
202004 APP_C CONN3 40 0.4 31 42
202002 APP_C CONN5 33 0.5 51 20
202003 APP_C CONN5 20 0.8 7 8
202004 APP_C CONN5 40 0.5 31 86
run;

PROC SORT data=CHART_TABLE
	out=CHAR_TABLE_SORT;
	by  App Conn Date;


ods layout gridded columns=2 Heights=8cm;
   
ods region;
options byline;/*this option removed the automatic titles that are added */


proc sgplot data=CHAR_TABLE_SORT;
	xaxis type=discrete display=(nolabel) fitpolicy=thin;
	yaxis grid;
	series x=Date y=Records / group=Conn;
	by App conn;
run;

ods region;
options nobyline;/*this option removes the automatic titles that are added */

proc report data=WORK.CHAR_TABLE_SORT nowd;
by App Conn;
	column  DATE  App Conn CV Mean Median Records;
	define DATE / group ;
	define App / group ;
	define Conn / group ;
	define CV / group;
	define Mean / group;
	define Median / group;
	define Records / sum;

  compute after _page_ / style={ BORDERCOLOR=white BORDERLEFTCOLOR=white};
    line ' ';
    line ' ';
    line ' ';
	line ' ';
    line ' ';
    line ' ';
    line ' ';
    line ' ';
    line ' ';
    line ' ';
    line ' ';
    line ' ';    
    line ' ';
    line ' ';
    line ' ';
    line ' ';
    line ' ';
    line ' ';
    line ' ';
    line ' ';

  endcomp;

run;

quit;

ods layout end;

(ps: I can't use ODS HTML as SAS and the folder structure I am in are in different environment, but that's not an issue as I can manually export the output to html).

thanks

Re: compute after page. line ' ' to change dynamically

Cynthia_sas — Thu, 03 Sep 2020 00:59:02 GMT

Hi:

If you take a step away from BY group processing and move to WHERE processing with a bit of SAS Macro code, then these results are possible without any LINE statement for positioning (sorry for the partial results, I was trying to fit as many on the browser screen as possible):

next groups:

I used your code and treated each APP/CONN unique combo as one "report" and then used a macro program to invoke that report over and over for multiple pairs. Here's the code I used to find the unique combinations of APP and CONN:


ods output list=work.owlist(drop=table f_app f_conn);
proc freq data=char_table_sort;
  tables app*conn/nocum nopercent list;
run;

proc print data=work.owlist noobs;
title 'To make the program more dynamic,';
title2 'could automate running the macro code from this file';
run;
title;

and that resulted in WORK.OWLIST, which looked like THIS:

Here's what the macro program definition looked like (color coded to show the use of the macro variables &WANTAPP and &WANTCONN:

Note that there are NOT any LINE statements needed in the PROC REPORT code for spacing purposes. Note also that the ODS HTML statements are not inside the macro program. And here's the ODS "sandwich" where the macro program was invoked for each unique APP/CONN combination:

Since there were a limited number of combinations, I just made manual calls to the macro program after I defined it. Although this is not as simple as using BY group processing, if the alignment from the BY group processing is not working for you, then this approach would eliminate the need for the LINE statements entirely.

Note that the ODS LAYOUT statements and the SGPLOT and PROC REPORT have been changed to only have WHERE instead of BY. Then the macro invocation is inside the ODS HTML "sandwich". When you use EG or SAS Studio, or even Display Manager, you are still using ODS HTML behind the scenes, the only difference is that ODS HTML statements allow you to explicitly name the file that you're creating.

The Macro program could be changed to be dynamic, using CALL EXECUTE or other techniques like %INCLUDE. This would mean no manual coding of APP/CONN combinations at all, but that is probably an example for a different post, assuming that you would be willing to switch to a different method in order to lose the LINE statements and get the alignment you want.

Cynthia

Re: compute after page. line ' ' to change dynamically

MART1 — Thu, 03 Sep 2020 17:17:00 GMT

Thank you so much @Cynthia_sas

In my "real" data the App Conn combination changes every day, so I'll definitely have to do it dynamically.

I've looked everywhere (I think!) but I can't find an example on how to use CALL EXECUTE on PROC SGPLOT or PROC REPORT - all papers talk about using it using it in a data step. Do you know where I could get any idea? (never used macros so everything is pretty new here).

Also, I have run your example, but it's putting everything in one column.

The only thing I changed is the ODS "sandwich", where I used LAYOUT instead of HTML (this is because the server where SAS lives and the folder structure I am in are in different environment, so I export to html using the Export function).

I don's see why but could this be the reason?

Here is the code I copied for you

%macro makereport(wantapp=,wantconn=);

ods layout gridded columns=2 Height=8cm;

ods region;

title "TEST";

proc sgplot data=CHAR_TABLE_SORT;

xaxis type=discrete display=(nolabel) fitpolicy=thin;
yaxis grid;

series x=Date y=Records / group=Conn;
where app="&wantapp" and conn="&wantconn";

run;


title;
ods region;

proc report data=WORK.char_table_sort nowd;
where app ="&wantapp" and conn="&wantconn";
column DATE App Conn cv Mean Median Records ;
define DATE / group;
define App / group;
define Conn / group;
define CV / group;
define Mean / group;
define Median / group;
define Records / sum;

run;
ods layout end;
%mend makereport;


title; footnote;
ods layout start;

%makereport(wantapp=APP_A, wantconn=CONN1)
%makereport(wantapp=APP_A, wantconn=CONN2)
%makereport(wantapp=APP_B, wantconn=CONN1)

ods layout end;

many thanks

Re: compute after page. line ' ' to change dynamically

Cynthia_sas — Thu, 03 Sep 2020 22:32:38 GMT

Hi:
The issue is that you need the controlling statements and can't get rid of them. I don't know how you're using SAS -- it almost sounds like you're using Enterprise Guide or one of the solution interfaces in which case, the default output that EG is creating could be the issue. Your SAS Administrator should be able to identify a location on the server where you can write output.

ODS LAYOUT and ODS REGION have to be supported by the destination, including the default destination that your interface is using. The ODS Sandwich is needed in order to be sure you are sending the output to a destination that supports ODS LAYOUT and ODS REGION.

Have you talked to your SAS Administrator about getting a location that you can write to? Otherwise, you could try to write the output to your WORK location and then navigate to your WORK location to find the output:
%let wpath=%sysfunc(pathname(WORK));
%put path to work location is: &wpath;

ods html(id=1) path="&wpath" (url=none)
file='wpath_layout.html';

%makereport(wantapp=APP_A, wantconn=CONN1)
%makereport(wantapp=APP_A, wantconn=CONN2)
%makereport(wantapp=APP_B, wantconn=CONN1)

** more invocations for macro program;
ods html(id=1) close;

And this shows that I got the 2 column output and the file is in my WORK location specified by the &WPATH macro variable:

I can show you an example of using CALL EXECUTE for the macro program. However, you need to figure out how to use the "full control" method of the ODS HTML sandwich in order to create your output.

My code works with ODS HTML, ODS HTML5, and ODS PDF to make 2 columns with the graph on the left and the table on the right. The code will NOT work in ODS RTF or ODS EXCEL (destinations that don't support ODS LAYOUT). The code also will not work in ODS LISTING or ODS CSV, which are also destinations that don't support ODS LAYOUT. If you look in your log, you should see a warning if the default destination does not support ODS LAYOUT:

As another example of how I would run this code in a server environment like SAS OnDemand for Academics, I would make an output folder under my Files (Home) directory in the OnDemand server and then do this:

ods html5(id=2) path='/home/<userid>/output/' (url=none)
file='soda_layout.html';

%makereport(wantapp=APP_A, wantconn=CONN1)
%makereport(wantapp=APP_A, wantconn=CONN2)
%makereport(wantapp=APP_B, wantconn=CONN1)

** more invocations for macro program;
ods html5(id=2) close;

Then I could use SAS Studio to go to the output folder to download the HTML file:

Cynthia

Example of Macro Program to invoke dynamically using CALL EXECUTE:


** need to run this PROC FREQ to create WORK.OWLIST from work.char_table_sort;

ods output list=work.owlist(drop=table f_app f_conn);
proc freq data=char_table_sort;
  tables app*conn/nocum nopercent list;
run;
 
title; footnote;
ods html(id=2) path='c:\temp' (url=none)
         file='dynamic_layout.html' gtitle;

data _null_;
  length arg $100;
  set work.owlist(keep=app conn);
  arg = catt('%nrstr(%makereport(wantapp=',app, ', wantconn=',conn,'))') ;
  call execute(arg);
run; 

ods html(id=2) close;

Using CALL EXECUTE is fairly advanced Macro programming, so you would really need to work on understanding Macro programming if you were going to use a method like CALL EXECUTE. There are other methods you could use, but you really need to resolve the server issue and file creation issue first.