Preserving Formatting in Excel via PROC EXPORT

Reply
Frequent Contributor
Posts: 81

Preserving Formatting in Excel via PROC EXPORT

[ Edited ]

I've been using PROC EXPORT with DBMS=XLSX to export data to Excel for a long time, but didn't think formatting could be preserved using this method (as compared with ODS EXCEL, etc).  I was updating some code however and noticed that some of the columns were formatted, while others weren't.  It turns out that only some of the formatting was being sent across however.  Variables formatted with COMMA20. were showing up as comma separated numbers in Excel, while those formatted as DOLLAR20. or PERCENT10.2 were unformatted.  Well actually the PERCENT ones were completely unformatted, while the variables with the DOLLAR format didn't have a currency sign or commas, but were hiding the cents columns (with the cents data still being there).

 

Here's an example of the format of an empty dataset I created and was adding data to prior to exporting to Excel:

 

DATA lib_A.TableA;
    /* CREATE EMPTY NEW DATASET WITH SEVERAL FORMATS DEFINED */
    FORMAT   Comparison_Group        $100.;
    FORMAT   IMPORT_Yr_Mth            $50.;
    FORMAT   Eligible_Txn         COMMA20.;
    FORMAT   Eligible_Amt        DOLLAR20.;
    STOP;
RUN;

 

Which I export to Excel like this:

PROC EXPORT DATA=lib_A.Table_A
     DBMS=XLSX REPLACE LABEL
     OUTFILE="&output_file";
     SHEET="&Sheet_Name";
RUN;

 

And looks like this in Excel:

Capture2.PNG

 

So are only some of the formats sent across using PROC EXPORT?  Is there a list of which ones work somewhere?  I searched around and didn't find anything obvious, although I did find one presentation on lexjansen.com that seemed to indicate that DOLLAR might pass through as well.  If possible I'd really like to be able to send percentage and dollar formatted values across and have them display properly.

 

Esteemed Advisor
Posts: 7,294

Re: Preserving Formatting in Excel via PROC EXPORT

Suggestion (if you're using Windows). Use the macro that @Tom@FriedEgg and I wrote a couple of years ago: http://www.sascommunity.org/wiki/A_Poor/Rich_SAS_Users_Proc_Export

 

The code you would use to run the macro would simply be:

 

%exportxl(
 data=have,
 outfile='c:\art\test2.xlsx',
 type=N,
 useformats=Y,
 usenames=Y,
 sheet=test)

although you would have to change data= to specify your actual dataset,

outfile= to specify the workbook you want to create and

sheet= to represent what you want to call the worksheet.

 

HTH,

Art, CEO, AnalystFinder.com

 

Frequent Contributor
Posts: 81

Re: Preserving Formatting in Excel via PROC EXPORT

[ Edited ]

@art297 Unfortunately the code will be running on Linux server via a crontab, so I can't use your macro, but I took a look anyways and it's quite nice.  It gave me a few ideas to improve some of the code in my macro libary.

Grand Advisor
Posts: 17,360

Re: Preserving Formatting in Excel via PROC EXPORT

Weird, I didn't think any formats were passed through when using PROC EXPORT.

 

Anyways, use ODS EXCEL with TAGATTR to customize the formats to exactly what you want. 

This assumes you have SAS 9.4TS1M3+ 

 

Otherwise you can try ODS TAGSETS.EXCELXP

Frequent Contributor
Posts: 81

Re: Preserving Formatting in Excel via PROC EXPORT

@Reeza Yeah, that's what I always thought too.  If nothing else I plan on switching all of the DOLLAR formatted variables to COMMA since that's closer to what I want.  I'll probably try out some other formats or variations to see if I can get anything else to pass through though.  I was hoping there was a list of working ones somewhere to save me the effort.

 

Currently my server is on 9.4M2, but we're scheduled to upgrade to M4 next week (for the second or third time, so we'll see if it actually happens). I may try ODS EXCEL once we finally get there.  I played around with the limited ODS EXCEL functionality in M2 a while back but ran into some problems so stuck with PROC EXPORT.

Frequent Contributor
Posts: 81

Re: Preserving Formatting in Excel via PROC EXPORT

I did some more exploring and haven't been able to find any other formats that seem to be passing through (of the ones I use anyways).  I also briefly tried creating some custom formats to see if I could get them to pass over, but no dice.  So at this point it looks like just the COMMA format works.  That's better than nothing.  

 

Maybe I'll try again once my server gets updated to 9.4M4 to see if that changes anything.

Valued Guide
Posts: 505

Re: Preserving Formatting in Excel via PROC EXPORT

I know this list overwhelmingly prefers using 'proc import' and 'proc export', so ignore this post if that is your preference.

Creating a highly stylized excel template and replacing, updating and inserting data in place into the template.

This is SAS differentiator (I think SAS would like to deprecate this functionality). WPS/R/Python implementations are not as clean, especially 
with regard to SAS (set/merge/update/modify) statements. Win 7 64bit, Office 2010 64bit, SAS 64bit and free MS ODBC/OLEDB drivers) 1. Create a highly stylized template using 'ods excel'. This is just the output of 'ods excel' Problem: 'ods excel' does not create a 'odbc' table. You need to either manually highlight the data and create a named range(odbc table) or use the short R SAS macro '%utl_submit_r64' below. 2. Once you have an odbc table you can update, replace or insert data keeping all style elements To execute the formulas on final sheet you need to go to formulas- show formulas - format column custom(general) - format formulas The single most powerful excel 'libname' festure is the automatic creation of an 'odbc/oledb' table with the same name as the sheet. I think SAS left this out of 'ods excel' to push 'proc export' and EG. Trivial for SAS to add odbc/oldb support to 'ods excel'? Your template does not have to have a primary key, you could use name. as primary key enables some more sophisticated in place single cell updates. HAVE (Excel template(no data except key) d:\xls\formulas.xlsx with 'ODBC' table class and sheet class) =================================================================================== +------------------+------------+------------+-----------+--------------------------+ | | A | B | C | D | E | +------+-----------+------------+------------+-----------+--------------------------+ | | KEY | NAME | HEIGHT | WEIGHT | BMI | +------+-----------+------------+------------+-----------+--------------------------+ | | | | | | | | 1 | 1 | [BOLD] | $##,###.## | ##,###.## |=(D2/(2.2*(C2/39.37)^2)) | | 2 | 2 | [BOLD] | $##,###.## | ##,###.## |=(D3/(2.2*(C3/39.37)^2)) | | 3 | 3 | [BOLD] | $##,###.## | ##,###.## |=(D4/(2.2*(C4/39.37)^2)) | | 4 | 4 | [YELLOW] | [YELLOW] | [YELLOW] | [YELLOW] | | .. | .. | .. | .. | ... | ... | | N | .. | .. | .. | ... | ... | +------------------+------------+------------+-----------+--------------------------+ [CLASS] WANT (fill in with data and maintain styles) ============================================== BOLD +------------------+------------+------------+-----------+-----------------+ | | A | B | C | D | E | +------+-----------+------------+------------+-----------+-----------------+ | | KEY | NAME | HEIGHT | WEIGHT | BMI | +------+-----------+------------+------------+-----------+-----------------+ | | | | | | | | 1 | 1 | Alfred | 690,000 |$1,125,000 | 0.001664802 | | 2 | 2 | Alice | 565,000 | $840,000 | 0.001853918 | | 3 | 3 | Barbara | 653,000 | $980,000 | 0.001619227 | | 4 | 4 | Carol | 628,000 |$1,025,000 | 0.001831103 | YELLOW | .. | .. | .. | .. | ... | ... | | N | .. | .. | .. | ... | ... | +------------------+------------+------------+-----------+-----------------+ [CLASS] FULL SOLUTION AFTER CREATING TEMPLATE libname xel "d:/xls/formulas.xlsx" scan_text=no; data xel.class; modify xel.class; set sd1.hasdata; run; libname xel clear; CREATE THE TEMPLATE =================== %let tpl=d:/xls/template_formulas.xlsx; * template no data use forward slashes -windows, unix python and R; %let final_xlsx=d:/xls/formulas.xlsx; * final workbook with data; * you need this macro to copy template to another workbook(templates dshoul not have data) %macro BinCop( in=\\filesrv04\stat\peggcsf\sd01\meta\datamart\bonepain_2008\docs\rogerdeangelis\bne.xls ,out=c:\pis\bne.xls ) / des="Copy a binary file"; data _null_; infile "&in" recfm=n; file "&out" recfm=n; input byt $char1. @@; put byt $char1. @@; run; %mend BinCop; * create some data (note the call missing for template.xlsx); data sd1.class(drop=bmi) sd1.hasdata; retain key 0; set sashelp.class; height=10000*height; weight=10000*weight; bmi=0; key=_n_; output sd1.hasdata; call missing(of _all_); key=_n_; output sd1.class; keep key name height weight bmi; run;quit; * create template(no data except key) with extensive formatting and a computed column with a formula; %utlfkil(&tpl.); * delete if exist; ods excel file="&tpl."; ods excel options(sheet_name="class" sheet_interval="none"); proc report data=sd1.class nowd; column key name height weight bmi; define key / display; define name / style(column)={font_weight=bold}; define height / display style={just=right tagattr='format:#,###,##0' cellwidth=1.1in};; define weight / display style={just=right tagattr='format:$#,###,##0' cellwidth=1.1in};; define bmi / computed "BMI" format=3. style={tagattr="formula:(RC[-1]/(2.2*(RC[-2]/39.37)^2))"}; compute bmi; bmi=0; endcomp; compute key; if key in (4,8,12,15) then call define(_row_, "Style", "Style = [background = yellow]"); endcomp; run; ods excel close; /* farmats are there but hidden Up to 40 obs from tbl.class total obs=20 {BOLD_ {COMMA} {DOLLAR} {FORMULA hidden} KEY NAME HEIGHT WEIGHT BMI 1 0 2 0 3 0 4 0 YELLOW but blank 5 0 6 0 7 0 ... ... 17 0 18 0 19 0 */ * create 'odbc' table in excel template; * would be a no brainer for SAS to also create an 'odbc' table with 'ods excel'?; %utl_submit_r64(" library(XLConnect); library(haven); datnew<-read_sas('d:/sd1/class.sas7bdat'); wb <- loadWorkbook('&tpl.', create = FALSE); setStyleAction(wb,XLC$'STYLE_ACTION.NONE'); createName(wb, name = 'class', formula = 'class!$A$1'); writeNamedRegion(wb, datnew, name = 'class'); saveWorkbook(wb); "); * copy template - do not want to destroy the template with data;; %bincop(in=&tpl.,out=&final_xlsx.); * this is all you need after creating the template; libname xel "&final_xlsx" scan_text=no; data xel.class; modify xel.class; set sd1.hasdata; run; libname xel clear; 3805 libname xel "&final_xlsx" scan_text=no; SYMBOLGEN: Macro variable FINAL_XLSX resolves to d:/xls/formulas.xlsx NOTE: Libref XEL was successfully assigned as follows: Engine: EXCEL Physical Name: d:/xls/formulas.xlsx 3806 data xel.class; 3807 modify xel.class; 3808 set sd1.hasdata; 3809 run; NOTE: There were 19 observations read from the data set XEL.class. NOTE: The data set XEL.class has been updated. There were 19 observations rewritten, 0 observations added and 0 observations deleted. NOTE: There were 19 observations read from the data set SD1.HASDATA. NOTE: DATA statement used (Total process time): real time 0.01 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 474.56k OS Memory 27164.00k Timestamp 03/10/2017 07:12:29 AM Step Count 636 Switch Count 0 3810 libname xel clear; NOTE: Libref XEL has been deassigned. * _ _ __ ___ (_)___ ___ | '_ ` _ \| / __|/ __| | | | | | | \__ \ (__ |_| |_| |_|_|___/\___| ; /* lets get the max lengths of some column */ proc sql dquote=ansi; connect to excel (Path="&final_xlsx"); select * from connection to Excel ( Select max(len(name)) as lennam ,max(len(key)) as lenkey from class ); disconnect from Excel; Quit; lennam lenkey ------------------------ 7 2 /* how many columns have at least one character cell */ proc sql dquote=ansi; connect to excel (Path="&final_xlsx"); select * from connection to Excel ( Select count(*) + sum(isnumeric(name)) as numchrname, count(*) + sum(isnumeric(weight)) as numchrwgt from class ); disconnect from Excel; Quit; numchrname numchrwgt -------------------- 19 0 proc sql dquote=ansi; connect to excel (Path="&final_xlsx"); create table cnv as select * from connection to Excel ( Select name ,str(now()) as curtym ,Switch(name="John","Table",name="Alice","Desk") AS TblDsk ,IIF(Weight>95, "Yes", "No") as onoff ,mid(name,1,1) as nammid ,format(weight,"####.00000000") as lonwgt from class ); disconnect from Excel; Quit; /* Up to 40 obs WORK.CNV total obs=19 Obs NAME CURTYM TBLDSK ONOFF NAMMID LONWGT 1 Alfred 3/10/2017 8:58:49 AM Yes A 1125000.00000000 2 Alice 3/10/2017 8:58:49 AM Desk Yes A 840000.00000000 3 Barbara 3/10/2017 8:58:49 AM Yes B 980000.00000000 4 Carol 3/10/2017 8:58:49 AM Yes C 1025000.00000000 5 Henry 3/10/2017 8:58:49 AM Yes H 1025000.00000000 6 James 3/10/2017 8:58:49 AM Yes J 830000.00000000 7 Jane 3/10/2017 8:58:49 AM Yes J 845000.00000000 8 Janet 3/10/2017 8:58:49 AM Yes J 1125000.00000000 9 Jeffrey 3/10/2017 8:58:49 AM Yes J 840000.00000000 10 John 3/10/2017 8:58:49 AM Table Yes J 995000.00000000 11 Joyce 3/10/2017 8:58:49 AM Yes J 505000.00000000 12 Judy 3/10/2017 8:58:49 AM Yes J 900000.00000000 13 Louise 3/10/2017 8:58:49 AM Yes L 770000.00000000 14 Mary 3/10/2017 8:58:49 AM Yes M 1120000.00000000 15 Philip 3/10/2017 8:58:49 AM Yes P 1500000.00000000 16 Robert 3/10/2017 8:58:49 AM Yes R 1280000.00000000 17 Ronald 3/10/2017 8:58:49 AM Yes R 1330000.00000000 18 Thomas 3/10/2017 8:58:49 AM Yes T 850000.00000000 19 William 3/10/2017 8:58:49 AM Yes W 1120000.00000000 */ * _ _ _ _ _ __ __| | __ _| |_ ___ | | | | '_ \ / _` |/ _` | __/ _ \ | |_| | |_) | (_| | (_| | || __/ \__,_| .__/ \__,_|\__,_|\__\___| |_| ; proc sql dquote=ansi; connect to excel (Path="&final_xlsx"); execute( update `d:/xls/formulas`.class class set weight=789 ,name='Roger' where key=2 ) by excel; disconnect from Excel; Quit; libname xel "&final_xlsx"; proc print data=xel.class(obs=2); run;quit; libname xel close; Obs KEY NAME HEIGHT WEIGHT 1 1 Alfred 690000 $1,125,000.00 2 2 Roger** 565000 $789.00** ** changed proc sql dquote=ansi; connect to excel (Path="&final_xlsx"); execute(insert into class values( 20, 'Zorba', 111,99)) by excel; disconnect from Excel; Quit; libname xel "&final_xlsx"; proc print data=xel.class; run;quit; libname xel close; /* * cannot insert between other observations but realtional databases are not ordered anyway; Obs KEY NAME HEIGHT WEIGHT 1 1 Alfred 690000 $1,125,000.00 2 2 Roger 565000 $789.00 3 3 Barbara 653000 $980,000.00 4 4 Carol 628000 $1,025,000.00 5 5 Henry 635000 $1,025,000.00 6 6 James 573000 $830,000.00 7 7 Jane 598000 $845,000.00 8 8 Janet 625000 $1,125,000.00 9 9 Jeffrey 625000 $840,000.00 10 10 John 590000 $995,000.00 11 11 Joyce 513000 $505,000.00 12 12 Judy 643000 $900,000.00 13 13 Louise 563000 $770,000.00 14 14 Mary 665000 $1,120,000.00 15 15 Philip 720000 $1,500,000.00 16 16 Robert 648000 $1,280,000.00 17 17 Ronald 670000 $1,330,000.00 18 18 Thomas 575000 $850,000.00 19 19 William 665000 $1,120,000.00 20 20 Zorba 111 $99.00 * added */ libname tbl excel "&final_xlsx" scan_text=no; proc sql; update tbl.class set height=199, name='Xena' where key=4; quit; libname tbl clear; libname tbl excel "&final_xlsx" scan_text=no; proc sql; update tbl.class set height=(select sum(height) from tbl.class where key<10) where key<5; ;quit; libname tbl clear; bs from tbl.class total obs= KEY NAME HEIGHT 1 Alfred 5592000 2 Roger 5592000 3 Barbara 5592000 4 Carol 5592000 5 Henry 635000 6 James 573000 7 Jane 598000 * _ __ __ ___| |_ _ _ / _|/ _| / __| __| | | | |_| |_ \__ \ |_| |_| | _| _| |___/\__|\__,_|_| |_| ; libname tbl excel "h:\xls\tables.xls" scan_text=no; proc sql; update tbl.demog set age=199, sex='none' where pat=12; quit; libname tbl clear; libname tbl excel "h:\xls\tables.xls" scan_text=no; proc sql; update tbl.play set wgt=(select sum(wgt) from tbl.play where pat<88888) where pat=99999; quit; libname tbl clear; /* declare columns as character */ proc sql dquote=ansi; connect to excel (Path="h:\xls\tables.xls"); execute( update `h:\xls\Tables`.jyn jyn set pat=' ', age=' ', treat=' ', lab= ' ', val= ' ' ) by excel; disconnect from Excel; Quit; libname tbl excel "h:\xls\tables.xls" scan_text=no; proc sql; create table trn as select put(l.pat,5.) as pat, put(l.age,5.) as age, c.treat as treat length=16 format=$16., r.lab as lab length=3 format=$3., put(r.val,5.2) as val from tbl.demog as l left join tbl.treatment as c on l.pat=c.pat left join tbl.lab as r on l.pat=r.pat order by pat; quit; proc contents data=tbl.jyn position; run; data tbl.jyn; n=_n_; modify tbl.jyn; set trn; run; libname tbl clear; /* does not work */ libname tbl excel "h:\xls\tables.xls" scan_text=no; proc contents data=tbl.sheet2; run; proc datasets library=tbl; modify sheet2; rename f1=pat; quit; proc contents data=tbl.sheet2; run; libname tbl clear; * exporting dataset to an excel sheet via WScript (vbs). Slow but flexible. Modified from data _null_^s SAS-L posting archived at UGA: http://tinyurl.com/yl9bvxb or http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0911a&L=sas-l&O=A&P=13475 chang_y_ch...@hotmail.com 20081104 */ On Mon, 2 Nov 2009 11:58:33 -0600, Data _null_; <iebup...@GMAIL.COM> wrote: ... >I don't now if this will be acceptable, but it does operate without >SAS/ACCESS to PC file formats. It does require EXCEL. ... Hi, _null_, Thanks for sharing a piece of excellent code. I would add that in order to run, it also requires Windows Script Host enabled. In terms of coding styles, it can be made clearer by separating (as much as we can) the window scripting vbs code from the sas data step code. Here is my try using a technique of expanding placeholders. I also love to clean up as much as I can. This ran on sas 9.2 on windows vista with excel 2007 installed locally. Cheers, Chang /* exporting dataset to an excel sheet via WScript (vbs). Slow but flexible. Modified from data _null_^s SAS-L posting archived at UGA: http://tinyurl.com/yl9bvxb or http://www.listserv.uga.edu/cgi-bin/wa?A2=ind0911a&L=sas-l&O=A&P=13475 chang_y_ch...@hotmail.com 20081104 */ %let data = sashelp.shoes; %let pwd = %sysfunc(pathname(WORK)); %let vbs = &pwd\sas2exl.vbs; %let xls = &pwd\sas2exl.xls; /* get the variable names and labels */ proc contents data=&data out=contents(keep=name label rename=(name=_name label=_label)) noprint; run; /* write a WSH script in vbs. substituting placeholders with data, if any */ data _null_; infile cards; file "&vbs"; length _inLine _outLine _value $32767; input; _inLine = _infile_; /* line without place holders */ if index(_inLine, "#row#") = 0 then do; _outLine = _inLine; link putLine; return; end; /* header row */ do _col = 1 by 1 while (not _headerDone); set contents end = _headerDone; _row = 1; _value = quote(trim(_name)); if not missing(_label) then do; _newLine = " & vbLF & vbLF & "; _label = quote(trim(_label)); _value = catx(_newLine, _value, _label); end; _valueQuote = 0; link expand; link putLine; end; _nCols = _col - 1; /* we get to know the n columns */ /* other rows */ do _row = 2 by 1 while (not _bodyDone); set &data end = _bodyDone; do _col = 1 to _nCols; set contents point=_col; _value = vvaluex(_name); _valueQuote = 1; link expand; link putLine; end; end; return; /* end of main */ /* utilities */ expand: _value = trim(_value); if _valueQuote then _value = quote(trim(_value)); _outLine = trim(tranwrd(tranwrd(tranwrd(_inLine, "#row#", strip(put(_row, best.))), "#col#", strip(put(_col, best.))), "#value#", _value) ); return; putLine: _outLine = trim(_outLine); _lineLength = length(_outLine); put _outLine $varying. _lineLength; return; /* vbs file template */ cards; ' sas2excel.vbs -- automatically generated ' open a sheet Set app = CreateObject("Excel.Application") app.Visible = True app.DisplayAlerts = False app.Workbooks.Add ' write data out Set sheet = app.ActiveWorkbook.Worksheets(1) sheet.Cells(#row#, #col#).Value = #value# ' a little bit of formatting sheet.UsedRange.columns.autofit ' save the excel sheet and clean up app.DisplayAlerts = False ' to overwrite app.ActiveWorkbook.SaveAs(WScript.Arguments(0)) app.Quit set sheet = nothing set app = nothing ; run; /* call the window script -- this should open excel */ filename cscript pipe "cscript ""&vbs"" ""&xls"""; data _null_; infile cscript end=end; do while(not end); input; put _infile_; end; stop; run; filename cscript clear; "Data _null_;" View profile More options Nov 4, 2:15 pm Newsgroups: comp.soft-sys.sas From: iebup...@GMAIL.COM ("Data _null_;") Date: Wed, 4 Nov 2009 16:15:44 -0600 Local: Wed, Nov 4 2009 2:15 pm Subject: Re: comment on recent sample 35574 at SAS Reply | Reply to author | Forward | Print | Individual message | Show original | Report this message | Find messages by this author Hi Chang, Good points all. I liked your technique of expanding placeholders when I first saw you post it and have used it from time to time. Getting the names in a data set is good too. I just wanted to use VNEXT. I think that instead of CONTENTS you could use TRANPOSE to build the CONTENTS data set. The will allow the order of variables to be explicitly specified or defaulted to data set order with _ALL_. You would have to make sure _LABEL is defined in the next data step as PROC TRANSPOSE won't create the variable if there are no labels. proc transpose data=&data(obs=0) name=_name label=_label out=contents; var _all_; * or other list of variables; *attrib _all_ label=' '; *for testing; run; It is indeed slow, but a bit faster with ?Visible=False?. Best regards, data _null_; On 11/4/09, Chang Chung <chang_y_ch...@hotmail.com> wrote:
Frequent Contributor
Posts: 81

Re: Preserving Formatting in Excel via PROC EXPORT

@rogerjdeangelis It's kind of hard to follow this since it isn't clear what's text and what's code, and it looks like there may be portions of SAS log files mixed in.  I gather however that the method you're outlining only works on a Windows system since I see references to OLEdb and possibly VBscript, but I'm running in a Linux environment and don't think those will work.  I actually already am familiar with multiple methods of exporting to Excel (those available in Linux at least), but am primarily looking for info on formats passing through to Excel via PROC EXPORT since I didn't think that was possible prior to yesterday.

Valued Guide
Posts: 505

Re: Preserving Formatting in Excel via PROC EXPORT

I suspect you have a windows laptop ot workstation and if you are a programmer all you need is 

 local SAS and shared folders with Unix box. 

 

In some ways you have great  tools in Unix, Perl, Python and R with packages XLConnect, Xlsx, Openxl and RODBC.

 

If you have IML you can interface with R.

Valued Guide
Posts: 505

Re: Preserving Formatting in Excel via PROC EXPORT

Ask a Question
Discussion stats
  • 9 replies
  • 550 views
  • 2 likes
  • 4 in conversation