topic Re: Help with proc export in SAS Programming

Help with proc export

Hm2 — Mon, 02 Nov 2015 19:16:14 GMT

Hi,

I am trying to export to and excel file to update a report daily. It keeps changing my format from a percentage to a decimal, how can I fix this:

proc export

data = LookToBook_MTD_90_180

outfile = "\\acc.local\fileservices\private\riskanalysis\shared\sales\Look to book by Territory\Key"

dbms = "XLSX" replace;

sheet = "Key";

run;

Re: Help with proc export

Reeza — Mon, 02 Nov 2015 19:56:41 GMT

That's mostly an Excel issue. Some workarounds are to use a different method of export - ODS (TAGSETS.EXCELXP or EXCEL).

Another is to write the field as a character string and then see if Excel converts it, though it may cause other issues in Excel.

Re: Help with proc export

SASKiwi — Wed, 04 Nov 2015 03:37:49 GMT

See this link for the default conversions that SAS performs based on the SAS format applied to the column - this is what you are experiencing:

http://support.sas.com/documentation/cdl/en/acpcref/67382/HTML/default/viewer.htm#n0cfqhcraieg81n1ier2cpp87fnz.htm

You might want export it from SAS formatted as a plain numeric then change it in Excel yourself, or if you want SAS to do it try Reeza's suggestions.