Good afternoon,
I am attempting to run a macro that makes trend plots for several categories and then exports them to word. However, I am hitting a few snags. First, the macro is only running the last set of commands without printing the previous command's graph. Second, the titles are not exporting to the RTF file.
I have tried a few ODS RTF commands such as "select all" and "body title", but neither kept the title. For clarity, the title shows in the output on enterprise guide but not on the actual RTF file when opened. I can give some sample data if needed, but the actual data is fairly lengthy.
My code looks like this:
*Money trend by DS on one frame;
%macro Quarter(data,category,out,label,title);
proc sql;
create table &category._&out. as select
mean(I_PreResYr1stT_&category) as Pre_Q1,
mean(I_PreResYr2ndT_&category) as Pre_Q2,
mean(I_PreResYr3rdT_&category) as Pre_Q3,
mean(I_PostResYr1stT_&category) as Pos_Q1,
mean(I_PostResYr2ndT_&category) as Pos_Q2,
mean(I_PostResYr3rdT_&category) as Pos_Q3
from &data
where TRICHOT_1YR ne . and Full_Alc_Exp = 1
group by TRICHOT_1YR;
quit;
proc transpose data=&category._&out. out=&category._&out.;run;
data &category._&out. ; set &category._&out. ;
rename COL1 =RA COL2 =UR COL3 =RNA; run;
data &category._&out.;
set &category._&out.;
Quadrimester = substr(_NAME_,5,2);
if substr(_NAME_,1,3)="Pos" then Year="Post Resolution";
if substr(_NAME_,1,3)="Pre" then Year="Pre Resolution";
drop _NAME_;
run;
ods graphics / reset=all border=off width=700px height=500px;
title height=0.2in "&title";
proc sgpanel data=&category._&out.;
panelby Year/sort=(descformat) headerattrs=(weight=bold);
series x=Quadrimester y=RA/
markers markerattrs=(symbol=circlefilled size=3pct color=blue) lineattrs=(color=blue thickness=4 pattern=longdash);
series x=Quadrimester y=UR/
markers markerattrs=(symbol=circlefilled size=3pct color=red) lineattrs=(color=red thickness=4 pattern=ShortDashDot);
series x=Quadrimester y=RNA/
markers markerattrs=(symbol=circlefilled size=3pct color=green) lineattrs=(color=green thickness=4 pattern=solid);
rowaxis label="&label" labelattrs=(size=12pt weight=bold) valueattrs=(size=12pt weight=bold);
colaxis label=' ' labelattrs=(size=12pt weight=bold) valueattrs=(size=12pt weight=bold);
keylegend /valueattrs=(Size=12 weight=bold);
run;
%mend;
ods rtf file="U:\Documents\ARC Expenditure and Integrity\SinglePaneTrendPlot_071519.rtf";
title 'New Category Full Alcohol and Expenditure (N=412): Quadrimester Trend Plots';
%Quarter(sub.newcatinflation2,TLFPL ,Q, Inflation-Adjusted Dollars, Quadrimester Trend for Financial and Legal Affairs );
%Quarter(sub.newcatinflation2,DurInsHouse ,Q, Inflation-Adjusted Dollars, Quadrimester Trend for Housing/Insurance/Durable Goods );
My data looks like this:
data WORK.CHECK;
infile datalines dsd truncover;
input PID:BEST. TRICHOT_1YR:BEST12. Full_ALC_Exp:BEST12. I_PreResYr1stTri_consume:32. I_PreResYr2ndTri_consume:32. I_PreResYr3rdTri_consume:32.;
format PID BEST. TRICHOT_1YR BEST12. Full_ALC_Exp BEST12.;
label PID="SUBJID#" TRICHOT_1YR="RA 273, UR 140, RNA 80, Missing 123" Full_ALC_Exp="1 year complete TLFB alcohol and spending data as well: 1=Yes, 0=No (number of yes is 412)";
datalines;
2609 . 0 1315.499163 1031.2530601 86.162580581
2670 . 0 . 689.00068096 689.00068096
2711 . 0 1856.2984793 1981.3589366 1570.1859747
2713 . 0 8835.8159545 9291.5286175 8472.5269738
2724 . 0 1727.4392094 1711.489481 1700.5554834
2743 . 0 1344.548653 1076.604699 1441.1998341
2776 . 0 1690.7046881 1690.7046881 1690.7046881
2812 . 0 342.74922238 338.67444067 338.67444067
2827 . 0 1085.1836237 1148.959131 1085.1836237
2854 . 0 2128.9891276 2407.0103218 2128.9891276
;;;;
So it looks like perhaps you are asking about the NOGTITLE option on the ODS RTF statement?
I commented out the extra two statements in your macro so that it ran and then ran it twice once with and without that option.
ods rtf file='c:\downloads\test1.rtf' nogtitle;
%Quarter(CHECK,TLFPL ,Q, Inflation-Adjusted Dollars, Quadrimester Trend for Financial and Legal Affairs );
ods rtf close;
ods rtf file='c:\downloads\test2.rtf' gtitle;
%Quarter(CHECK,TLFPL ,Q, Inflation-Adjusted Dollars, Quadrimester Trend for Financial and Legal Affairs );
ods rtf close;
I use Enterprise Guide 7.12.
I checked running a basic macro with the SASHELP.CLASS dataset and the titles work. I went back to my program and I am having the below problem:
Could anyone explain why the same graph is being output for each variable within the results for SAS but the RTF file has each of the separate graphs? The results section is only outputting the last macro command for each of the graphs, but using the different titles. Whereas, the RTF file has the separate graphs but no titles?
Ive attached the file and output pictures for clarity. This is running the same code as above but changing the Y axis label.
Here is the log:
1 The SAS System 14:30 Monday, July 15, 2019
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='TrendPlot071519';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='S:\Alcstudy\ComboDataEntry\Joe\Inflation\Subcategories\MasterDataset and RegressionLogProp1.egp';
6 %LET _CLIENTPROJECTNAME='MasterDataset and RegressionLogProp1.egp';
7 %LET _SASPROGRAMFILE='S:\Alcstudy\ComboDataEntry\Joe\Inflation\Subcategories\TrendPlot071519.sas';
8
9 ODS _ALL_ CLOSE;
10 OPTIONS DEV=ACTIVEX;
11 GOPTIONS XPIXELS=0 YPIXELS=0;
12 FILENAME EGSR TEMP;
13 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14 STYLE=HtmlBlue
15 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
16 NOGTITLE
17 NOGFOOTNOTE
18 GPATH=&sasworklocation
19 ENCODING=UTF8
20 options(rolap="on")
21 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22
23 GOPTIONS ACCESSIBLE;
24 *Money trend by DS on one frame;
25 %macro Quarter(data,category,out,label,title);
26 proc sql;
27 create table &category._&out. as select
28 mean(I_PreResYr1stT_&category) as Pre_Q1,
29 mean(I_PreResYr2ndT_&category) as Pre_Q2,
30 mean(I_PreResYr3rdT_&category) as Pre_Q3,
31 mean(I_PostResYr1stT_&category) as Pos_Q1,
32 mean(I_PostResYr2ndT_&category) as Pos_Q2,
33 mean(I_PostResYr3rdT_&category) as Pos_Q3
34 from &data
35 where TRICHOT_1YR ne . and Full_Alc_Exp = 1
36 group by TRICHOT_1YR;
37 quit;
38
39 proc transpose data=&category._&out. out=&category._&out.;run;
40
41 data &category._&out. ; set &category._&out. ;
42 rename COL1 =RA COL2 =UR COL3 =RNA; run;
43
44 data &category._&out.;
45 set &category._&out.;
46 Quadrimester = substr(_NAME_,5,2);
47 if substr(_NAME_,1,3)="Pos" then Year="Post Resolution";
48 if substr(_NAME_,1,3)="Pre" then Year="Pre Resolution";
49 drop _NAME_;
50 run;
51
52 ods graphics / reset=all border=off width=700px height=500px;
53 title height=0.2in "&title";
54 proc sgpanel data=&category._&out.;
55 panelby Year/sort=(descformat) headerattrs=(weight=bold);
56 series x=Quadrimester y=RA/
57 markers markerattrs=(symbol=circlefilled size=3pct color=blue) lineattrs=(color=blue thickness=4 pattern=longdash);
2 The SAS System 14:30 Monday, July 15, 2019
58 series x=Quadrimester y=UR/
59 markers markerattrs=(symbol=circlefilled size=3pct color=red) lineattrs=(color=red thickness=4 pattern=ShortDashDot);
60 series x=Quadrimester y=RNA/
61 markers markerattrs=(symbol=circlefilled size=3pct color=green) lineattrs=(color=green thickness=4 pattern=solid);
62
63 rowaxis label="&label" labelattrs=(size=12pt weight=bold) valueattrs=(size=12pt weight=bold);
64 colaxis label=' ' labelattrs=(size=12pt weight=bold) valueattrs=(size=12pt weight=bold);
65 keylegend /valueattrs=(Size=12 weight=bold);
66 run;
67 %mend;
68
69 ods rtf bodytitle file="U:\Documents\ARC Expenditure and Integrity\SinglePaneTrendPlot_071519.rtf";
NOTE: Writing RTF Body file: U:\Documents\ARC Expenditure and Integrity\SinglePaneTrendPlot_071519.rtf
70 title 'New Category Full Alcohol and Expenditure (N=412): Quadrimester Trend Plots';
71
72 %Quarter(sub.newcatinflation2,TLFPL ,Q, Inflation-Adjusted Dollars, Quadrimester Trend for Financial and Legal Affairs );
NOTE: Table WORK.TLFPL_Q created, with 3 rows and 6 columns.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.51 seconds
cpu time 0.04 seconds
NOTE: There were 3 observations read from the data set WORK.TLFPL_Q.
NOTE: The data set WORK.TLFPL_Q has 6 observations and 4 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
NOTE: There were 6 observations read from the data set WORK.TLFPL_Q.
NOTE: The data set WORK.TLFPL_Q has 6 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
NOTE: There were 6 observations read from the data set WORK.TLFPL_Q.
NOTE: The data set WORK.TLFPL_Q has 6 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
NOTE: PROCEDURE SGPANEL used (Total process time):
real time 0.49 seconds
cpu time 0.18 seconds
NOTE: There were 6 observations read from the data set WORK.TLFPL_Q.
73 %Quarter(sub.newcatinflation2,DurInsHouse ,Q, Inflation-Adjusted Dollars, Quadrimester Trend for Housing/Insurance/Durable Goods );
NOTE: Table WORK.DURINSHOUSE_Q created, with 3 rows and 6 columns.
NOTE: PROCEDURE SQL used (Total process time):
3 The SAS System 14:30 Monday, July 15, 2019
real time 0.04 seconds
cpu time 0.03 seconds
NOTE: There were 3 observations read from the data set WORK.DURINSHOUSE_Q.
NOTE: The data set WORK.DURINSHOUSE_Q has 6 observations and 4 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
NOTE: There were 6 observations read from the data set WORK.DURINSHOUSE_Q.
NOTE: The data set WORK.DURINSHOUSE_Q has 6 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
NOTE: There were 6 observations read from the data set WORK.DURINSHOUSE_Q.
NOTE: The data set WORK.DURINSHOUSE_Q has 6 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
NOTE: PROCEDURE SGPANEL used (Total process time):
real time 0.28 seconds
cpu time 0.07 seconds
NOTE: There were 6 observations read from the data set WORK.DURINSHOUSE_Q.
74
75 ods rtf close;
76
77 GOPTIONS NOACCESSIBLE;
78 %LET _CLIENTTASKLABEL=;
79 %LET _CLIENTPROCESSFLOWNAME=;
80 %LET _CLIENTPROJECTPATH=;
81 %LET _CLIENTPROJECTNAME=;
82 %LET _SASPROGRAMFILE=;
83
84 ;*';*";*/;quit;run;
85 ODS _ALL_ CLOSE;
86
87
88 QUIT; RUN;
89
To me, everything looks kosher here.
How do you use RTF Close? I am using it like this:
ods rtf bodytitle file="U:\Documents\ARC Expenditure and Integrity\SinglePaneTrendPlot_071519.rtf";
title 'New Category Full Alcohol and Expenditure (N=412): Quadrimester Trend Plots';
%Quarter(sub.newcatinflation2,TLFPL ,Q, Inflation-Adjusted Dollars, Quadrimester Trend for Financial and Legal Affairs );
%Quarter(sub.newcatinflation2,DurInsHouse ,Q, Inflation-Adjusted Dollars, Quadrimester Trend for Housing/Insurance/Durable Goods );
ods rtf close;
I attempted to attach the RTF file which shows that each macro was invoked and used the new category. For instance, TLFPL and DurInsHouse will have different graphs in the RTF file but no titles- not even in the header. Whereas the "results" on the SAS Report are the pictures above. They have different titles, but they appear to only be graphing the last macro command evoked. In this case, that was the DurInsHouse.
1 updated log
The SAS System 14:30 Monday, July 15, 2019
1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='TrendPlot071519';
4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5 %LET _CLIENTPROJECTPATH='S:\Alcstudy\ComboDataEntry\Joe\Inflation\Subcategories\MasterDataset and RegressionLogProp1.egp';
6 %LET _CLIENTPROJECTNAME='MasterDataset and RegressionLogProp1.egp';
7 %LET _SASPROGRAMFILE='S:\Alcstudy\ComboDataEntry\Joe\Inflation\Subcategories\TrendPlot071519.sas';
8
9 ODS _ALL_ CLOSE;
10 OPTIONS DEV=ACTIVEX;
11 GOPTIONS XPIXELS=0 YPIXELS=0;
12 FILENAME EGSR TEMP;
13 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14 STYLE=HtmlBlue
15 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
16 NOGTITLE
17 NOGFOOTNOTE
18 GPATH=&sasworklocation
19 ENCODING=UTF8
20 options(rolap="on")
21 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22
23 GOPTIONS ACCESSIBLE;
24 *Money trend by DS on one frame;
25 %macro Quarter(data,category,out,label,title);
26 proc sql;
27 create table &category._&out. as select
28 mean(I_PreResYr1stT_&category) as Pre_Q1,
29 mean(I_PreResYr2ndT_&category) as Pre_Q2,
30 mean(I_PreResYr3rdT_&category) as Pre_Q3,
31 mean(I_PostResYr1stT_&category) as Pos_Q1,
32 mean(I_PostResYr2ndT_&category) as Pos_Q2,
33 mean(I_PostResYr3rdT_&category) as Pos_Q3
34 from &data
35 where TRICHOT_1YR ne . and Full_Alc_Exp = 1
36 group by TRICHOT_1YR;
37 quit;
38
39 proc transpose data=&category._&out. out=&category._&out.;run;
40
41 data &category._&out. ; set &category._&out. ;
42 rename COL1 =RA COL2 =UR COL3 =RNA; run;
43
44 data &category._&out.;
45 set &category._&out.;
46 Quadrimester = substr(_NAME_,5,2);
47 if substr(_NAME_,1,3)="Pos" then Year="Post Resolution";
48 if substr(_NAME_,1,3)="Pre" then Year="Pre Resolution";
49 drop _NAME_;
50 run;
51
52 ods graphics / reset=all border=off width=700px height=500px;
53 title height=0.2in "&title";
54 proc sgpanel data=&category._&out.;
55 panelby Year/sort=(descformat) headerattrs=(weight=bold);
56 series x=Quadrimester y=RA/
57 markers markerattrs=(symbol=circlefilled size=3pct color=blue) lineattrs=(color=blue thickness=4 pattern=longdash);
2 The SAS System 14:30 Monday, July 15, 2019
58 series x=Quadrimester y=UR/
59 markers markerattrs=(symbol=circlefilled size=3pct color=red) lineattrs=(color=red thickness=4 pattern=ShortDashDot);
60 series x=Quadrimester y=RNA/
61 markers markerattrs=(symbol=circlefilled size=3pct color=green) lineattrs=(color=green thickness=4 pattern=solid);
62
63 rowaxis label="&label" labelattrs=(size=12pt weight=bold) valueattrs=(size=12pt weight=bold);
64 colaxis label=' ' labelattrs=(size=12pt weight=bold) valueattrs=(size=12pt weight=bold);
65 keylegend /valueattrs=(Size=12 weight=bold);
66 run;
67 %mend;
68
69 ods rtf file="U:\Documents\ARC Expenditure and Integrity\SinglePaneTrendPlot_071519.rtf" bodytitle;
NOTE: Writing RTF Body file: U:\Documents\ARC Expenditure and Integrity\SinglePaneTrendPlot_071519.rtf
70 title2 'New Category Full Alcohol and Expenditure (N=412): Quadrimester Trend Plots';
71
72 %Quarter(sub.newcatinflation2,TLFPL ,Q, Inflation-Adjusted Dollars, Quadrimester Trend for Financial and Legal Affairs );
NOTE: Table WORK.TLFPL_Q created, with 3 rows and 6 columns.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.50 seconds
cpu time 0.09 seconds
NOTE: There were 3 observations read from the data set WORK.TLFPL_Q.
NOTE: The data set WORK.TLFPL_Q has 6 observations and 4 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
NOTE: There were 6 observations read from the data set WORK.TLFPL_Q.
NOTE: The data set WORK.TLFPL_Q has 6 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.05 seconds
cpu time 0.00 seconds
NOTE: There were 6 observations read from the data set WORK.TLFPL_Q.
NOTE: The data set WORK.TLFPL_Q has 6 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
NOTE: PROCEDURE SGPANEL used (Total process time):
real time 0.40 seconds
cpu time 0.12 seconds
NOTE: There were 6 observations read from the data set WORK.TLFPL_Q.
73 %Quarter(sub.newcatinflation2,DurInsHouse ,Q, Inflation-Adjusted Dollars, Quadrimester Trend for Housing/Insurance/Durable Goods );
NOTE: Table WORK.DURINSHOUSE_Q created, with 3 rows and 6 columns.
NOTE: PROCEDURE SQL used (Total process time):
3 The SAS System 14:30 Monday, July 15, 2019
real time 0.04 seconds
cpu time 0.01 seconds
NOTE: There were 3 observations read from the data set WORK.DURINSHOUSE_Q.
NOTE: The data set WORK.DURINSHOUSE_Q has 6 observations and 4 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
NOTE: There were 6 observations read from the data set WORK.DURINSHOUSE_Q.
NOTE: The data set WORK.DURINSHOUSE_Q has 6 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
NOTE: There were 6 observations read from the data set WORK.DURINSHOUSE_Q.
NOTE: The data set WORK.DURINSHOUSE_Q has 6 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds
NOTE: PROCEDURE SGPANEL used (Total process time):
real time 0.28 seconds
cpu time 0.11 seconds
NOTE: There were 6 observations read from the data set WORK.DURINSHOUSE_Q.
74
75 ods rtf close;
76
77 GOPTIONS NOACCESSIBLE;
78 %LET _CLIENTTASKLABEL=;
79 %LET _CLIENTPROCESSFLOWNAME=;
80 %LET _CLIENTPROJECTPATH=;
81 %LET _CLIENTPROJECTNAME=;
82 %LET _SASPROGRAMFILE=;
83
84 ;*';*";*/;quit;run;
85 ODS _ALL_ CLOSE;
86
87
88 QUIT; RUN;
89
@Reeza wrote:
You have to change the macro TITLE. Order of operations, the TITLE will come before TITLE2....TITLE2 needs to be in the macro.
What @Reeza is saying is that when you set title line N then that also clears title lines N+1 to 10. So your program is running a TITLE2 and then a TITLE (aka TITLE1) statement. So any title set by TITLE2 is removed.
Do both of your graphs appear on the same page? A page can only have one header area, which is where titles are placed.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.