Help using Base SAS procedures

Error in PROC EXPORT

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Error in PROC EXPORT

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


Accepted Solutions
Solution
‎01-10-2017 08:55 AM
Super User
Super User
Posts: 7,961

Re: Error in PROC EXPORT

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.

View solution in original post


All Replies
Super User
Posts: 7,803

Re: Error in PROC EXPORT

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎01-10-2017 08:55 AM
Super User
Super User
Posts: 7,961

Re: Error in PROC EXPORT

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 192 views
  • 0 likes
  • 3 in conversation