BookmarkSubscribeRSS Feed
WilliamD_
Fluorite | Level 6

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

6 REPLIES 6
LinusH
Tourmaline | Level 20

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.

Data never sleeps
WilliamD_
Fluorite | Level 6

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.

WilliamD_
Fluorite | Level 6

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.

Peter_C
Rhodochrosite | Level 12

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

WilliamD_
Fluorite | Level 6

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

pparpati11
Calcite | Level 5

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

sas-innovate-2024.png

 

Secure your spot at the must-attend AI and analytics event of 2024: SAS Innovate 2024! Get ready for a jam-packed agenda featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events.

 

Register by March 1 to snag the Early Bird rate of just $695! Don't miss out on this exclusive offer. 

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2979 views
  • 0 likes
  • 4 in conversation