BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dougall
Fluorite | Level 6

Hi,

 

I am testing ODS Excel to see if we should move our report generation from tagsets.excelxp.

Our reports generally had one Title Line with a Black background and white text.

I have been testing the ODS Excel with proc odstext to generate titles, but I have been unable to make this title merge across the report columns to fully display the text.

Using SAS9.4 1M3

 

I have included some sample code, does anyone know a method to merge this title across in ODS Excel?

 

Thank you

 

dm log 'clear' log;

 

data test;

input Var1 Var2 Var3 Var4 $;

datalines;

20 25 30 Long line of text

1 8 64 Long line of text

14 32 38 Short text

15 12 12 The longest line of text of all time

;

run;

 

ods listing close;

ods excel file="&outPath\Test.xlsx"

            options(embedded_titles='yes'

                        Title_Footnote_nobreak='yes'

                        title_footnote_width='8'

                        sheet_name='Custom_Sheet'

                        title_footnote_nobreak='yes'

                        sheet_interval='none'

                        );

ods escapechar="^";

 

proc odstext;

p "This is a test, it is not a real dataset. " / style=[font_weight=bold font_size=15pt color=white backgroundcolor=black nobreakspace=yes];

run;

 

proc print data=test;

run;

 

ods excel close;

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

Instead of using Proc ODSTEXT, I suggest to use a TITLE statement, this will automatically span the columns of your output. something like

 

 

title color=cxffffff bcolor=cx000000 height=15pt bold 
  "This is a test, it is not a real dataset. "
;

If you want to add multiple table and have something like a header for each table that spawns the number of columns you have in the table, you could use the tech below using Proc REPORT with the COMPUTE BEFORE _PAGE_

 


proc report data=sashelp.class;
  column ("Span" name age sex height weight);
  compute before _page_ / 
    style=[font_weight=bold font_size=15pt color=white backgroundcolor=black]
  ;
    xTitle = "This is a test, it is not a real dataset. ";
    len = length(xTitle);
    line xTitle $varying1024. len;
  endcomp;
run;

proc report data=sashelp.cars;
  column make model type origin invoice horsepower;
    compute before _page_ / 
    style=[font_weight=bold font_size=15pt color=white backgroundcolor=black]
  ;
    xTitle = "This is a test, it is not a real dataset. ";
    len = length(xTitle);
    line xTitle $varying1024. len;
  endcomp;
run;

 

View solution in original post

5 REPLIES 5
BrunoMueller
SAS Super FREQ

Instead of using Proc ODSTEXT, I suggest to use a TITLE statement, this will automatically span the columns of your output. something like

 

 

title color=cxffffff bcolor=cx000000 height=15pt bold 
  "This is a test, it is not a real dataset. "
;

If you want to add multiple table and have something like a header for each table that spawns the number of columns you have in the table, you could use the tech below using Proc REPORT with the COMPUTE BEFORE _PAGE_

 


proc report data=sashelp.class;
  column ("Span" name age sex height weight);
  compute before _page_ / 
    style=[font_weight=bold font_size=15pt color=white backgroundcolor=black]
  ;
    xTitle = "This is a test, it is not a real dataset. ";
    len = length(xTitle);
    line xTitle $varying1024. len;
  endcomp;
run;

proc report data=sashelp.cars;
  column make model type origin invoice horsepower;
    compute before _page_ / 
    style=[font_weight=bold font_size=15pt color=white backgroundcolor=black]
  ;
    xTitle = "This is a test, it is not a real dataset. ";
    len = length(xTitle);
    line xTitle $varying1024. len;
  endcomp;
run;

 

Dougall
Fluorite | Level 6

Awesome, that's working for me.

 

Thanks for the help!

Cynthia_sas
SAS Super FREQ

Hi:

  The challenge with what you're trying is that PROC ODSTEXT will write your entire string in column A, that's what I expect, at least. Even though you are setting the title_footnote_width suboption, that will only impact what is specified in a TITLE or FOOTNOTE statement. So that setting will NOT even touch the ODSTEXT.

 

  However, using some slightly different test data:

data test;
length var4 $100;
  infile datalines dsd dlm=',';
input Var1 Var2 Var3 Var4 $;
datalines;
14, 32, 38, "Short text"
15, 12, 12, "The longest line of text of all time"
20, 25, 30, "Twas brillig and the slithy toves did gyre and gimble in the wabe."
;
run;

I was able to generate this using PROC REPORT without using ODSTEXT or a TITLE statement:

proc_report_alternative.png

 

but if you WANT to use PROC PRINT and a TITLE statement instead of ODSTEXT, then what you want can be done using simpler ODS EXCEL syntax, but you need a style template to make the SystemTitle change:

print_title_template.png

 

 

I don't think PROC ODSTEXT is going to work for what you're trying to do in this instance. Perhaps try one of these other alternatives instead.

 

Cynthia

 

 

 

Dougall
Fluorite | Level 6

Thanks for the help!

 

I tried to simplify my example, so we actually are using proc reports with compute blocks. I will play around with Title Statements and compute statements, so far those have been working.

Cynthia_sas
SAS Super FREQ
The SystemTitle style template example will work with other procedures, too. It will impact the TITLE statement which is not procedure specific. I just meant if you wanted the SystemTitle to have a black background with ODS EXCEL you need to use a STYLE template approach.

cynthia

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 3346 views
  • 2 likes
  • 3 in conversation