topic Re: Proc Export to UNIX Directory W/ PC Files Interface in SAS Procedures

Proc Export to UNIX Directory W/ PC Files Interface

WilliamD_ — Tue, 14 Jan 2014 22:50:05 GMT

Hey everyone,

Recently, my business decided to upgrade all windows machines to Microsoft Windows 7 (from XP). I, and my team, develop our code using Base SAS 9.2 for UNIX. Until the switch to WIN 7, XP would accept files generated via Proc Export DBMS = XLS. Most of our programs are designed to export into .xls and then email as an attachment to either a hand full of people or a SharePoint site. Because the XLS is no longer an option for WIN 7, we have started toying around with the SAS/Access Interface to PC Files.

Example:

LIBNAME MYMDB PCFILES SERVER= &CGCPCIP. PATH='d:\sas\example.xls';

DATA MYMDB.CLASS;

SET SASHELP.CLASS;

RUN;

LIBNAME MYMDB CLEAR;

Or:

PROC EXPORT DATA = SASHELP.CLASS

DBMS = EXCELCS

OUTFILE = 'd:\sas\example.xls'

REPLACE;

SERVER= &CGCPCIP.;

RUN;

Does anyone know of a way to save these files on the UNIX box versus being forced to save onto the machine designated as the "server"? I don't believe there is a way to generate an email from UNIX using a file that doesn't exist on the machine, and if I can't email it without having to manually move the file back to UNIX - I can't batch it.

Any ideas would be greatly appreciated!

William

Re: Proc Export to UNIX Directory W/ PC Files Interface

LinusH — Wed, 15 Jan 2014 08:27:30 GMT

It's not clear to me how your whole environment looks like. Are you using SAS via some kind of X server? If so, upgrading Windows shouldn't affect your SAS processing.

You should also consider upgrading SAS, from 9.3, at least PROC EXPORT offers the EXCEL data source which is compliant with current MS Office formats.

When using the PC Files Server, I think that the interaction only can take place on a file system available to that server. So if you want the files on UNIX, have the PC Files Server running on UNIX. Please describe a bit more about how, when and to whom you wish to email your files.

Re: Proc Export to UNIX Directory W/ PC Files Interface

WilliamD_ — Wed, 15 Jan 2014 14:31:30 GMT

Hi Linus, I'm using a Xterm to remote into the UNIX server where I write and process my SAS programs. All of my teams original code is written using the DBMS = XLS which works for people still using Windows XP, but everyone who's migrated to Windows 7 cannot. This is a known issue due to the fact that the XLS output is designed for Microsoft Office 2003 and it commonly fails when trying to open in WIN 7 64bit running Office 32 bit.

The only work around to make an xls that is usable in WIN 7 is to either use DBMS tab - but I can't expect my customers to know how to import the data into a table properly and I can't cheat my naming the extension .xls. The data I work with has numerical values that often times exceed 15 digits, so most of the exports that fail to include metadata are unusable to me. As far as email - the data is usually emailed monthly as the programs finish collecting their data.

Unfortunately, you cannot run the PC Files server on UNIX as it's designed to run on Windows as a way to make a Microsoft compliant file.

I work for a large Corp... Upgrading to Base SAS 9.3 for UNIX isn't an option: the server needs to be upgraded first and there is already development to upgrade to 9.4/9.5 in 2015.

Re: Proc Export to UNIX Directory W/ PC Files Interface

WilliamD_ — Thu, 06 Mar 2014 17:53:13 GMT

I thought I'd follow up with an update to this problem.

The PC Files Server running on the windows machine defaults to no user specific login credentials... The read/write permissions are inherited by the login credentials of the service, not the user logged into the machine itself. Essentially, you need to either setup a new user profile with the appropriate drive read/write access for all directories you intend to write to or you input your own login credentials and manually run the service daily (delayed start doesn't always work depending on network setup). Because of the security risks involved in this, unfortunately I've had to abandon this method.

I've since started using ODS tagset.excelxp and then declaring my var / style = {tagattr='FORMAT:TEXT'}; to prevent truncation of numerical data upon opening in excel. This method will allow Windows 7 Office 2010 to open the .xls extension files but not .xlsx extension files.

Re: Proc Export to UNIX Directory W/ PC Files Interface

Peter_C — Thu, 06 Mar 2014 18:14:26 GMT

WilliamD. wrote:
I've since started using ODS tagset.excelxp and then declaring my var / style = {tagattr='FORMAT:TEXT'}; to prevent truncation of numerical data upon opening in excel.  This method will allow Windows 7 Office 2010 to open the .xls extension files but not .xlsx extension files.

For the files you create with tagsets.excelxp, try a filename with a .XML extension

Re: Proc Export to UNIX Directory W/ PC Files Interface

WilliamD_ — Thu, 06 Mar 2014 18:23:13 GMT

Hey Peter, while that does work better for the excelxp tagset, my customers prefer the .xls format.

Re: Proc Export to UNIX Directory W/ PC Files Interface

pparpati11 — Tue, 20 Feb 2018 19:45:33 GMT

Hi,

I am trying to do something similar. It works fine with a local path on the PC where PC files serer is Installed. We have Base SAS on a UNIX server and I am trying to run the code below fro SAS EG. The below will run fine if I also specify username and password, but I dont want to hard code my credentials in the code. Wanted to ask if there are any workarounds . I need to export to both excel and access.

PROC EXPORT data=sashelp.cars
dbms=excelcs
outfile="\sharepoint Location\test_cars.xls" replace;
sheet = Data;
server="10.47.87.96" ;
port=9621;
run;

PROC EXPORT DATA=sashelp.cars
OUTTABLE="cars1"
DBMS=ACCESSCS REPLACE ;
DATABASE="\SharePoint location\test_cars.MDB";
SERVER="10.47.87.96";
PORT=9621;
RUN;

Thanks,
Praveena