topic Re: Title not Exporting to RTF in ODS and Base Reporting

Title not Exporting to RTF

joebacon — Mon, 15 Jul 2019 20:10:43 GMT

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
;;;;

Re: Title not Exporting to RTF

Reeza — Mon, 15 Jul 2019 20:13:03 GMT

Titles go in the header of the document unless you specify BODYTITLE in your ODS RTF statement.

I also don't see an RTF CLOSE statement, which is probably the issue with not seeing the results.

Re: Title not Exporting to RTF

joebacon — Mon, 15 Jul 2019 20:52:09 GMT

Hi Reeza,

Thank you for taking your time to look this over.

I checked that! It was only giving me the date and time in the headers even after putting the BODYTITLE in the ODS RTF statement.

I forgot to add the RTF CLOSE statement in this code, but I definitely have it.

Re: Title not Exporting to RTF

Reeza — Mon, 15 Jul 2019 21:00:31 GMT

What version of SAS are you using? If it's an older version it's possible it doesn't have certain features, but in general what you ahve should work. Can you test with a base case, print SASHELP.CLASS with titles and see what happens?

BTW you have two title statements, the latter will overwrite the first. One after your ODS RTF and one in the macro. Only the macro one will show.

Re: Title not Exporting to RTF

joebacon — Tue, 16 Jul 2019 14:56:23 GMT

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.

Re: Title not Exporting to RTF

Tom — Tue, 16 Jul 2019 15:02:13 GMT

Do both of your graphs appear on the same page? A page can only have one header area, which is where titles are placed.

Re: Title not Exporting to RTF

Reeza — Tue, 16 Jul 2019 15:02:17 GMT

Show the log.

Usually the error of having some graphs appear and others not is because 1) one option is erroring out - check the log, or 2) You didn't use ODS RFT CLOSE properly, which would also be in the log. So time to see the log. Not sure what you mean about 'same graphs' vs graphs in the RTF file. Can you be more specific?

Re: Title not Exporting to RTF

joebacon — Tue, 16 Jul 2019 15:05:09 GMT

Both of the graphs do not appear on the same page. I didn't use that RFT command.

I attached the RTF file it was generating which is not showing any titles in the header. Rather, it shows the date and time that it was created even though I have title statements.

Re: Title not Exporting to RTF

Reeza — Tue, 16 Jul 2019 15:05:21 GMT

@Tom wrote:

Do both of your graphs appear on the same page? A page can only have one header area, which is where titles are placed.

Or unless you use BODYTITLE which puts it in the main page area. If it's in the header, well, each page only has one header so I'm assuming each graph will go to a separate page anyways so each would have it's own header. Unless STARTPAGE was also set.

Re: Title not Exporting to RTF

joebacon — Tue, 16 Jul 2019 15:09:58 GMT

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.

Re: Title not Exporting to RTF

Reeza — Tue, 16 Jul 2019 15:15:02 GMT

You never fixed the title statement so that won't work still. Change one of them, the second one to title2.

And put bodytitle after your file in the ODS RTF statement.

Re: Title not Exporting to RTF

joebacon — Tue, 16 Jul 2019 15:18:41 GMT

I made those quick changes. Both the RTF file and the SAS results look the same.

Re: Title not Exporting to RTF

joebacon — Tue, 16 Jul 2019 15:21:33 GMT

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

Re: Title not Exporting to RTF

Reeza — Tue, 16 Jul 2019 15:22:31 GMT

You have to change the macro TITLE. Order of operations, the TITLE will come before TITLE2....TITLE2 needs to be in the macro.

Re: Title not Exporting to RTF

Tom — Tue, 16 Jul 2019 15:27:21 GMT

Can you post a working data step for your sample data? As data that works with your macro?

122   %Quarter(CHECK,TLFPL  ,Q, Inflation-Adjusted Dollars, Quadrimester Trend for Financial and Legal
122 ! Affairs );
MPRINT(QUARTER):   proc sql;
MPRINT(QUARTER):   create table TLFPL_Q as select mean(I_PreResYr1stT_TLFPL) as Pre_Q1
,mean(I_PreResYr2ndT_TLFPL) as Pre_Q2 ,mean(I_PreResYr3rdT_TLFPL) as Pre_Q3 ,mean(I_PostResYr1stT_TLFPL)
as Pos_Q1 ,mean(I_PostResYr2ndT_TLFPL) as Pos_Q2 ,mean(I_PostResYr3rdT_TLFPL) as Pos_Q3 from CHECK where
TRICHOT_1YR ne . and Full_Alc_Exp = 1 group by TRICHOT_1YR ;
ERROR: The MEAN summary function requires a numeric argument.
ERROR: The MEAN summary function requires a numeric argument.
ERROR: The MEAN summary function requires a numeric argument.
ERROR: The MEAN summary function requires a numeric argument.
ERROR: The MEAN summary function requires a numeric argument.
ERROR: The MEAN summary function requires a numeric argument.
ERROR: The following columns were not found in the contributing tables: I_PostResYr1stT_TLFPL,
       I_PostResYr2ndT_TLFPL, I_PostResYr3rdT_TLFPL, I_PreResYr1stT_TLFPL, I_PreResYr2ndT_TLFPL,
       I_PreResYr3rdT_TLFPL.
MPRINT(QUARTER):   quit;

Re: Title not Exporting to RTF

ballardw — Tue, 16 Jul 2019 15:29:32 GMT

When I run your data step for work.check I get lots of errors.

16   data WORK.CHECK;
17     infile datalines dsd truncover;
18     input PID:BEST. TRICHOT_1YR:BEST12. Full_ALC_Exp:BEST12. I_PreResYr1stTri_consume:32.
18 ! I_PreResYr2ndTri_consume:32. I_PreResYr3rdTri_consume:32.;
19     format PID BEST. TRICHOT_1YR BEST12. Full_ALC_Exp BEST12.;
20     label PID="SUBJID#" TRICHOT_1YR="RA 273, UR 140, RNA 80, Missing 123" Full_ALC_Exp="1 year
20 ! complete TLFB alcohol and spending data as well: 1=Yes, 0=No (number of yes is 412)";
21   datalines;

NOTE: Invalid data for PID in line 22 1-46.
RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+--
22         2609 . 0 1315.499163 1031.2530601 86.162580581
PID=. TRICHOT_1YR=. Full_ALC_Exp=. I_PreResYr1stTri_consume=. I_PreResYr2ndTri_consume=.
I_PreResYr3rdTri_consume=. _ERROR_=1 _N_=1
NOTE: Invalid data for PID in line 23 1-36.
23         2670 . 0 . 689.00068096 689.00068096
PID=. TRICHOT_1YR=. Full_ALC_Exp=. I_PreResYr1stTri_consume=. I_PreResYr2ndTri_consume=.
I_PreResYr3rdTri_consume=. _ERROR_=1 _N_=2
NOTE: Invalid data for PID in line 24 1-47.
24         2711 . 0 1856.2984793 1981.3589366 1570.1859747
PID=. TRICHOT_1YR=. Full_ALC_Exp=. I_PreResYr1stTri_consume=. I_PreResYr2ndTri_consume=.
I_PreResYr3rdTri_consume=. _ERROR_=1 _N_=3
NOTE: Invalid data for PID in line 25 1-47.
25         2713 . 0 8835.8159545 9291.5286175 8472.5269738
PID=. TRICHOT_1YR=. Full_ALC_Exp=. I_PreResYr1stTri_consume=. I_PreResYr2ndTri_consume=.
I_PreResYr3rdTri_consume=. _ERROR_=1 _N_=4
NOTE: Invalid data for PID in line 26 1-46.
26         2724 . 0 1727.4392094 1711.489481 1700.5554834
PID=. TRICHOT_1YR=. Full_ALC_Exp=. I_PreResYr1stTri_consume=. I_PreResYr2ndTri_consume=.
I_PreResYr3rdTri_consume=. _ERROR_=1 _N_=5
NOTE: Invalid data for PID in line 27 1-45.
RULE:      ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+--
27         2743 . 0 1344.548653 1076.604699 1441.1998341
PID=. TRICHOT_1YR=. Full_ALC_Exp=. I_PreResYr1stTri_consume=. I_PreResYr2ndTri_consume=.
I_PreResYr3rdTri_consume=. _ERROR_=1 _N_=6
NOTE: Invalid data for PID in line 28 1-47.
28         2776 . 0 1690.7046881 1690.7046881 1690.7046881
PID=. TRICHOT_1YR=. Full_ALC_Exp=. I_PreResYr1stTri_consume=. I_PreResYr2ndTri_consume=.
I_PreResYr3rdTri_consume=. _ERROR_=1 _N_=7
NOTE: Invalid data for PID in line 29 1-47.
29         2812 . 0 342.74922238 338.67444067 338.67444067
PID=. TRICHOT_1YR=. Full_ALC_Exp=. I_PreResYr1stTri_consume=. I_PreResYr2ndTri_consume=.
I_PreResYr3rdTri_consume=. _ERROR_=1 _N_=8
NOTE: Invalid data for PID in line 30 1-46.
30         2827 . 0 1085.1836237 1148.959131 1085.1836237
PID=. TRICHOT_1YR=. Full_ALC_Exp=. I_PreResYr1stTri_consume=. I_PreResYr2ndTri_consume=.
I_PreResYr3rdTri_consume=. _ERROR_=1 _N_=9
NOTE: Invalid data for PID in line 31 1-47.
31         2854 . 0 2128.9891276 2407.0103218 2128.9891276
PID=. TRICHOT_1YR=. Full_ALC_Exp=. I_PreResYr1stTri_consume=. I_PreResYr2ndTri_consume=.
I_PreResYr3rdTri_consume=. _ERROR_=1 _N_=10
NOTE: The data set WORK.CHECK has 10 observations and 6 variables.
NOTE: DATA statement used (Total process time):
      real time           0.06 seconds
      cpu time            0.04 seconds

Re: Title not Exporting to RTF

Tom — Tue, 16 Jul 2019 15:30:59 GMT

@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.

Re: Title not Exporting to RTF

joebacon — Tue, 16 Jul 2019 16:13:41 GMT

I apologize! I ran the macro twice as the variables are slightly differently named - "Tri" in the middle or "T". This should be working data with minimal missing values that will work with that macro.

data WORK.CHECK;
  infile datalines dsd truncover;

input PID:BEST. TRICHOT_1YR:BEST12. Full_ALC_Exp:BEST12. I_PreResYr1stT_TLFPL:32. I_PreResYr1stT_DurInsHouse:32. I_PreResYr2ndT_TLFPL:32. I_PreResYr2ndT_DurInsHouse:32. I_PreResYr3rdT_TLFPL:32. I_PreResYr3rdT_DurInsHouse:32. I_PostResYr1stT_TLFPL:32. I_Pos
tResYr1stT_DurInsHouse:32. I_PostResYr2ndT_TLFPL:32. I_PostResYr2ndT_DurInsHouse:32. I_PostResYr3rdT_TLFPL:32. I_PostResYr3rdT_DurInsHouse: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;
2605 1 1 11166.131119 11707.922833 12561.772509 10928.509261 12561.772509 11462.570587 11078.105658 12811.94131 12462.743865 9628.9003835 1 9259.5419486
2623 1 1 . . 1 2685.3534532 21.72939979 3928.259141 83.26388002 4293.022259 62.697910015 2363.3350257 83.26388002 1939.9124927
2631 1 1 1 646.6652924 1 646.6652924 1 896.86059321 138.1064667 973.05039329 1 2373.0558331 1 6704.8905622
2657 1 1 14040.469694 4417.4101195 13694.979697 5280.2878307 14264.789439 5186.8225015 12743.455645 4016.3032487 13436.501413 5197.3785994 13779.267579 8491.561087
2663 1 1 1 2546.9228145 1 2059.4455187 1 2059.4455187 1 2012.0441376 1 2014.4096266 1 2463.6632996
2666 1 1 1 1623.2340472 788.71719202 1486.0301725 1 1348.8262979 1 1574.8386887 225.96429056 1719.9220147 547.34184851 2159.9029707
2674 1 1 1 1666.8164544 1 1666.8164544 1 1159.9691999 1 1394.0364786 1 2060.1108743 1 2060.1108743
2675 1 1 1 5634.4296762 1 4270.461746 1 3156.6891166 1 3790.8682689 1 1869.9402586 1 2388.179488
2684 1 1 5734.1577391 6168.4849518 6121.5764016 6168.4849518 5354.9893778 7782.6481828 14017.284406 9847.0086851 14280.775613 8314.3848387 16531.92669 8102.3742713
2688 1 1 1 1970.8471458 1 1 1 969.4979386 138.1064667 3099.0337125 1325.8323664 3213.3545793 983.45009779 9467.7676662
;;;;
NOTE: There were 10 observations read from the data set WORK.CHECK.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

@Tom

Re: Title not Exporting to RTF

Tom — Tue, 16 Jul 2019 16:19:11 GMT

That seems a little close to what the macro is expecting (once you remove the unwanted DSD option from the INFILE statement).

But still it does not have everything the macro is expecting.

The output of the PROC TRANSPOSE step does not produce any COL2 or COL3 variable to be renamed.

MPRINT(QUARTER):   data TLFPL_Q ;
MPRINT(QUARTER):   set TLFPL_Q ;
MPRINT(QUARTER):   rename COL1 =RA COL2 =UR COL3 =RNA;
MPRINT(QUARTER):   Quadrimester = substr(_NAME_,5,2);
MPRINT(QUARTER):   if substr(_NAME_,1,3)="Pos" then Year="Post Resolution";
MPRINT(QUARTER):   if substr(_NAME_,1,3)="Pre" then Year="Pre Resolution";
MPRINT(QUARTER):   drop _NAME_;
MPRINT(QUARTER):   run;

WARNING: The variable COL2 in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable COL3 in the DROP, KEEP, or RENAME list has never been referenced.
NOTE: There were 6 observations read from the data set WORK.TLFPL_Q.
NOTE: The data set WORK.TLFPL_Q has 6 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


MPRINT(QUARTER):   ods graphics / reset=all border=off width=700px height=500px;
MPRINT(QUARTER):   title height=0.2in "Quadrimester Trend for Financial and Legal Affairs";
MPRINT(QUARTER):   proc sgpanel data=TLFPL_Q;
MPRINT(QUARTER):   panelby Year/sort=(descformat) headerattrs=(weight=bold);
MPRINT(QUARTER):   series x=Quadrimester y=RA/ markers markerattrs=(symbol=circlefilled size=3pct
color=blue) lineattrs=(color=blue thickness=4 pattern=longdash) ;
ERROR: Variable UR not found.
MPRINT(QUARTER):   series x=Quadrimester y=UR/ markers markerattrs=(symbol=circlefilled size=3pct
color=red) lineattrs=(color=red thickness=4 pattern=ShortDashDot) ;
ERROR: Variable RNA not found.
MPRINT(QUARTER):   series x=Quadrimester y=RNA/ markers markerattrs=(symbol=circlefilled size=3pct
color=green) lineattrs=(color=green thickness=4 pattern=solid) ;
MPRINT(QUARTER):   rowaxis label="Inflation-Adjusted Dollars" labelattrs=(size=12pt weight=bold)
valueattrs=(size=12pt weight=bold);
MPRINT(QUARTER):   colaxis label=' ' labelattrs=(size=12pt weight=bold) valueattrs=(size=12pt weight=bold);
MPRINT(QUARTER):   keylegend /valueattrs=(Size=12 weight=bold);
MPRINT(QUARTER):   run;

Re: Title not Exporting to RTF

joebacon — Tue, 16 Jul 2019 16:25:09 GMT

Man, I commend you for sticking with this as long as you could to help me. I didn't want to give you the full dataset as its quite large.

I handpicked these PID's to make sure there are varying Trichot_1Yr so you get 3 columns.

data WORK.CHECK;
  infile datalines  truncover;

input PID:BEST. TRICHOT_1YR:BEST12. Full_ALC_Exp:BEST12. I_PreResYr1stT_TLFPL:32. I_PreResYr1stT_DurInsHouse:32. I_PreResYr2ndT_TLFPL:32. I_PreResYr2ndT_DurInsHouse:32. I_PreResYr3rdT_TLFPL:32. I_PreResYr3rdT_DurInsHouse:32. I_PostResYr1stT_TLFPL:32. I_Pos
tResYr1stT_DurInsHouse:32. I_PostResYr2ndT_TLFPL:32. I_PostResYr2ndT_DurInsHouse:32. I_PostResYr3rdT_TLFPL:32. I_PostResYr3rdT_DurInsHouse: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;
2605 1 1 11166.131119 11707.922833 12561.772509 10928.509261 12561.772509 11462.570587 11078.105658 12811.94131 12462.743865 9628.9003835 1 9259.5419486
2858 1 1 1388.517443 4424.1157183 2924.10987 4222.5505804 3328.5739468 4312.0054148 2730.681917 2168.4999609 1300.0109658 2391.6101145 1104.1285743 2592.6901461
6338 1 1 1 3469.2203908 1 2775.5763127 153.6345391 2081.9322345 452.12863105 4834.3025103 407.01576795 2695.0336416 6.012540345 2604.3434301
6360 1 1 1 1291.126601 1 1033.1012808 1 861.98296562 1 1444.4520149 1 1313.9125079 1 1223.4778124
2638 2 1 1 1 1 1 1 833.56925292 275.2129334 1244.6006528 549.4258668 861.40720293 789.36218353 861.40720293
2680 2 1 1 1 222.11359776 1 . . 1 1 1 263.47858875 1 1
5063 2 1 3036.70267 5460.0842347 12893.195568 1811.5354958 708.75239392 1065.096051 2961.5388348 23270.379643 8158.0570494 2200.0573564 8053.2596138 2925.4710269
2822 3 1 4081.288449 1968.5412537 4081.288449 3928.7488641 4081.288449 2275.2663763 4223.9541787 2449.1101817 4021.3172219 10861.549044 4061.8446133 11396.164002
5155 3 1 3289.967007 3243.9862507 4294.9291481 6558.8840384 3289.967007 6439.902004 3220.9384279 6038.407469 3767.9332221 5703.232567 3282.9687654 5703.232567
6207 3 1 1 17451.859419 6779.3136817 98733.113819 1 11065.349002 6962.005 12244.89575 8663.584 14332.7113 9127.651 10562.465
;;;;