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

Hi guys,

I'm exporting my final analysis as an excel file using proc export. Is there a way that I can make the header bold and blue before exporting?

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

Hi:
Bottom line, when you are using PROC EXPORT, you are exporting a plain table, which does not have colors and fonts. On the other hand, if you used PROC PRINT to print your table, you could send the report to one of the ODS (Output Delivery System) destinations for Excel. With a report, you can have colors and fonts in the output. With a data table, you cannot impact colors and fonts with PROC EXPORT.
cynthia

 

Here's an example to test out. You will need to have SAS 9.4 M2 to use ODS EXCEL:

 

ods excel file='c:\temp\colors.xlsx'
    options(embedded_titles='yes' sheet_name='Colors');
 
proc print data=sashelp.class noobs
     style(header)={background=pink color=purple};
  title 'This is the Title';
  var name /
      style(data)={color=purple fontweight=bold};
  var age sex /
      style(data)={background=lightgray};
  var height weight/
      style(data)={background=lightyellow};
run;
 
ods excel close;

View solution in original post

7 REPLIES 7
Cynthia_sas
SAS Super FREQ

Hi:
Bottom line, when you are using PROC EXPORT, you are exporting a plain table, which does not have colors and fonts. On the other hand, if you used PROC PRINT to print your table, you could send the report to one of the ODS (Output Delivery System) destinations for Excel. With a report, you can have colors and fonts in the output. With a data table, you cannot impact colors and fonts with PROC EXPORT.
cynthia

 

Here's an example to test out. You will need to have SAS 9.4 M2 to use ODS EXCEL:

 

ods excel file='c:\temp\colors.xlsx'
    options(embedded_titles='yes' sheet_name='Colors');
 
proc print data=sashelp.class noobs
     style(header)={background=pink color=purple};
  title 'This is the Title';
  var name /
      style(data)={color=purple fontweight=bold};
  var age sex /
      style(data)={background=lightgray};
  var height weight/
      style(data)={background=lightyellow};
run;
 
ods excel close;
parmis
Fluorite | Level 6

Cynthia,

could you please give me an example for this? I'm very confused.

 

Thanks

parmis
Fluorite | Level 6

Thanks Cynthia for your example.

I have tried to execute the following query but it doesn't  work. It gives me syntax error.

ods excel file='C:\Desktop\Ad_std.xlsx'

options(embedded_titles='yes' sheet_name='Pymt');

 

 

proc print data=sasuser.Ad_std noobs

style(header)={color=lightblue};

title 'Payments Method';

var Payments /

style={color=lightgray fontweight=bold};

var counts /

style={background=lightgray fontweight=bold};

var Percentage/

style={background=lightgray fontweight=bold};

run;

 

 ods excel close;

 

Cynthia_sas
SAS Super FREQ
Hi: What is the syntax error you get? If you run MY code, exactly as I typed it, does MY code work for you ?? (My code uses SASHELP.CLASS so you should be able to run it).

You might have an issue writing to your Desktop, that is one guess I have. The other guess is that your STYLE override does NOT conform to the syntax I posted.

Another reason it might not work is that you might not have SAS 9.4 M2 and therefore, ODS EXCEL is not available to you. Without knowing the log message, the exact message, that you're getting, it's hard to make any other suggestions.

cynthia
parmis
Fluorite | Level 6

hi Cynthia: no it doesn't work. it gives an error for the word "background" and "color".

the error is: ERROR 22-322: sytnx error, expecting one of the following: ;, style.

 

Cynthia_sas
SAS Super FREQ

Hi:

  I'd suggest that you carefully compare the VAR statements in your posted code, to the VAR statements in my original code. You did not follow my model. My VAR statement had style(data)=, while your code (for some reason) did NOT follow the model code I posted as shown below. My model code with style(data)= is at the top, your code is underneath with just style=:

code_diff.png

If I change my (working) code to use what you posted without the style(data), then my code gets an error.

get_error_wrong_syntax.png

So, my suggestion is that you fix your code to use the correct form of the style override option on the VAR statement, as I showed in my original code. The error is not with the background attribute. The error is because you did not specify the override correctly. PROC TABULATE uses STYLE= without an area, but both PROC PRINT and PROC REPORT need to have an area specified after STYLE.

 

You can do a lot with style overrides:

print_overrides.png

 

But you need to specify them correctly -- here's some code (again) to use as a model:

proc print data=sashelp.class(obs=3) n
  style(obsheader)={background=lightred color=black}
  style(obs)={background=white color=red}
  style(n)={fontsize=14pt fontweight=bold color=black background=white};
  var name age sex /
    style(header)={background=yellow color=black}
	style(data)={color=navy background=lightblue};
  var height weight /
    style(header)={background=lightgreen color=purple}
	style(data)={color=darkgreen background=cxeeeeee};
run;

Notice how I have specified each area in parentheses after the STYLE option, always style(area)={attr=value} .

 

Hope this helps,

 

cynthia

 

parmis
Fluorite | Level 6

Thank you so much Cynthia. It works pretty well

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 7 replies
  • 7096 views
  • 4 likes
  • 2 in conversation