topic Re: Error in PROC EXPORT in SAS Procedures

Error in PROC EXPORT

RJ2016 — Tue, 10 Jan 2017 09:45:23 GMT

Hi Is there a way to proc export to network area XLSX currently it fails so having to export as xlsb.

I can proc export xlsx to UNIX area then use a 'copy files to/from server ' task to put on network, however it seems to change the formatting within the excel like dates

proc export dbms=excelcs data=masteroutput outfile="\\thu.network2\ut\Master&file_date.&version..xlsb" replace;
   version="2007";
   sheet="master";
   server='*******************';
   port=3245;
RUN;

FYI: SERVER DETAILS HASHED OUT

Re: Error in PROC EXPORT

Kurt_Bremser — Tue, 10 Jan 2017 09:57:16 GMT

Use csv as transfer format (and write with a manually created data step), so you can check the contents with a text editor.

Note that you have to supply data in formats that Excel interprets correctly, so you will have to tweak your process until you get the wanted result.

Welcome to the world of Excel idiosyncrasies.

Re: Error in PROC EXPORT

RW9 — Tue, 10 Jan 2017 10:17:24 GMT

Hi,

Well, if your going from Unix system (where SAS is installed yes?) to a Windows network path then I would imagine you will need to ftp the file across. Unix/Win based systems are different.

As for your other problem, Excel is a really bad choice for data. It has built in "functionality" which alters your data for viewing purposes in its best guess. It needs to recognise that something is a date to display it as a date for instance. Proc export is really a very basic function for the export to Excel, again I wouldn't suggest using that for any purpose. If you need a nice viewable report in Excel, then use ods tagsets.excelxp, or ods excel or something along those lines. Then in your proc report you can apply Excel specific formatting amongst many other things. That being said, I would only use Excel for reports - i.e. something not to be used for anything further - and even then only after trying one of the other options such as PDF. For any kind of data transfer use an open plain text based format such as CSV or XML.