Data Export to excel with formatting

Reply
Regular Contributor
Posts: 215

Data Export to excel with formatting

can anyone tell me what would be the code for exporting data to excel and format at the same time?

My table look like this in SAS (year is yy, last_name is not in order, variable name is not bold):

Enroll_dateFirst_NameLast_NameBirthdate
09-Jan-14AlbertRobino08-Oct-45
06-Dec-13JohnMoscovici12-Nov-30
17-Jan-14MichaelNoor18-Sep-78

I want my excel table to bring the year as yyyy, last name ascending, variable name bold. It should look like this:

Enroll_dateFirst_NameLast_NameBirthdate
06-Dec-2013JohnMoscovici12-Nov-1930
17-Jan-2014MichaelNoor18-Sep-1978
09-Jan-2014AlbertRobino08-Oct-1945

can anyone tell me what would be the code for this? I am using sas 9.4 and office 2010

Grand Advisor
Posts: 10,251

Re: Data Export to excel with formatting

Proc Export will not do what you want. Tagsets.excelxp will create XML that Excel can read and with procedures such as Proc Print, Report and Tabulate provide some of what you want.

The digits in the date variable are likely controlled by the Format associated which looks like it is currently DATE9. Use Date11. for output. The ORDER of appearance would likely want a SORT step.

Proc sort data=have; by Lastname; run;

ods tagsets.excelxp file="C:\folder\myfile.xml" ;

proc print data=have noobs;

var enroll_date first_name last_name birthdate; /* or the order you want the variables to appear*/

format enroll_date birthdate date11.;

run;

ods tagsets.excelxp close;

open the result with excel. The active style will control some font information but column headings in most will be bold.

Grand Advisor
Posts: 17,464

Re: Data Export to excel with formatting

If you have SAS 9.4 you can also try ODS EXCEL but it's still experimental and doesn't work in all circumstances. Just switch the ODS TAGSETS.EXCELXP with ODS EXCEL. 

It looks like you might want to customize the width of the columns, you can look into the TAGSETS options to see which ones to set to customize your column widths.

Instead of PROC PRINT consider PROC REPORT which will allow you to create bold headers and/or customized column widths.

Base SAS: Quick Reference for TAGSETS.EXCELXP Tagset

Ask a Question
Discussion stats
  • 2 replies
  • 239 views
  • 0 likes
  • 3 in conversation