topic Putting Free text above a title line without the output in ODS and Base Reporting

Putting Free text above a title line without the output

ssitharath0420 — Fri, 19 Mar 2021 21:21:52 GMT

Is there a way to put free texts above your title within your output? I tried it but it gave me an error that it needs to be placed by a group.

Below is my code:

ods excel file="\\fenrir\DEVL\sam_s\Standard Opioid ODS Conversion\Summary_test.xlsx" options(start_at="1,1"
frozen_headers="5"
frozen_rowheaders="3"
/*autofilter="1-5"*/
sheet_name="Summary"
row_repeat="1"
embedded_titles="yes");

proc report split='~' data=Table1_Final out=Table1_Final_1 spanrows style(header)=[fontweight=bold background=lightblue foreground=black]
style(column)={font_face='Calibri' fontsize=11pt};
column Status P4 P3 P2 P1;
define Status / Display "";
define P1 / Analysis "7/1/20 -~ 12/31/2020" style(column)=[cellwidth=15% textalign=center just=decimal];
define P2 / Analysis "4/1/20 -~ 09/30/2020" style(column)=[cellwidth=15% textalign=center just=decimal];
define P3 / Analysis "1/1/20 -~ 06/30/2020" style(column)=[cellwidth=15% textalign=center just=decimal];
define P4 / Analysis "10/1/19 -~ 03/31/2020" style(column)=[cellwidth=15% textalign=center just=decimal];
title1 '# of Members Filling Opioids';
endcomp;
compute before _Page_ / left;
length text0 - text6 $100;
if _BREAK_= ' ' then
do;
text0="OPIOID OVERUTILIZATION";

text1="HEALTH PLAN GROUP: &Clnt_Name";

text2="CLIENT id: &clntid";

text3="Date Generated: &To_Date";

text4="Evaluation Period: &Eval_Date";

text5="Data Sources: PDW";
end;
line text0 $100.;
line text1 $100.;
line text2 $100.;
line text3 $100.;
line text4 $100.;
line text5 $100.;
endcomp;
compute before Status;
line '';
line '';
endcomp;
run;

ods excel close;

I want it to look like the attachment Example.xlsx but when I run my code it looked like \Summary_test.xlsx

Re: Putting Free text above a title line without the output

ballardw — Fri, 19 Mar 2021 21:53:51 GMT

You don't show a TITLE statement in that code so I am not sure how to interpret your requirement.

If you mean text before the entire procedure you can use either ODS TEXT or Proc ODSTEXT to create text that goes to the ODS destination. You will need to modify the Sheet_interval option to get the output on the same sheet as the Proc Report output.

Many users here don't want to download Excel files because of virus potential, organization policy or others have such things blocked by security software.

If you want something to "look like" then provide an image.

Re: Putting Free text above a title line without the output

Cynthia_sas — Mon, 22 Mar 2021 01:43:57 GMT

Hi: I could not open your XLSX files, there was an error of some kind on my system. I do see your TITLE statement, however, placing the title statement in the middle of the PROC REPORT isn't going to change anything. The TITLE statement is a GLOBAL statement and it is pulled out and handled first, so it is put at the top of the physical or logical page. Your PROC REPORT (all of your PROC REPORT) including the LINE statement output will happen AFTER the TITLE statement. So even without seeing your output, my guess is that you think the TITLE will be in the middle of the PROC REPORT, but it is at the top, above your LINE statements.

I think I would do the COMPUTE BEFORE _PAGE_ lines as TITLES instead, and move your current TITLE (title1 '# of Members Filling Opioids';) to be the LINE statement for the BEFORE, instead.

But again, I could not see your Excel sheets. And without data, no one can test or tweak your code anyway.

Cynthia