Proc sgplot by - add a table beneath each chart

[MART1]

Hello

I have created a series of charts (Proc Sgplot), and I'm now trying to add tables beneath.

Here is an example with dummy data:

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 advance=bygroup;

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

ods layout end;

Beneath each chart, I'd like to have a table similar to the below (i.e. this will be for the first chart)

(This should be done dynamically, as the number of APPs can vary)

I assume if I "break" the table by APP, I can then use the ods layout, by I'm struggling to achieve that.

Many thanks

[Rick_SAS]

If I understand correctly, you have a bunch of graphs (one for each BY group) and then a bunch of tables (also one for each BY group). What you need to do is to write the output to the ODS DOCUMENT, then use PROC DOCUMENT to replay the output in the order you want. You can start by reading the article "Reorder the output from a BY-group analysis in SAS." It contains an example and links to many resources that go into more detail.

An alternative is to use the INSET statement or an Annotation data set to put each table on the graph, such as in the lower margin.

[MART1]

Hello Rick and many thanks for this

First time I encountered the Proc Document option and I will surely use it again.

Can I ask one thing: I can't reorganise the output to have each table beneath the relative chart; I've played around with the REPLAY option but not sure how to make it work

.

Below is what I've done so far

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 document name=doc(write);
	ods layout gridded        columns=2 advance=bygroup;

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

PROC PRINT DATA=CHART_TABLE (where=(DATE=202004));
	VAR  App Conn Records CV Mean Median;
	BY App;
RUN;

ods layout end;
ods document close;

many thanks