05-01-2012 09:51 AM
I'm trying to write a stored process that will read in a MS Access file, and make some plots. I'm stuck on how to read in the access file.
The SAS server is a linux box, and we have SAS/ACCESS to PC files. We don't have SAS/SHARE.
The access file is sitting on a windows file server.
1. With this set up, am I forced to have the stored process upload the file from the windows server to the SAS server, so that the stored process can see the data? (I think so, but didn't know if there is some way to magically span the gap between windows and linux by using an infomap or DI studio or registering a libref or....)
2. If I do need to upload the file, is there a way to set up a prompt to upload a file (using EG 4.3) without writing my own html form? http://support.sas.com/documentation/cdl/en/stpug/61271/HTML/default/viewer.htm#a003254547.htm
I think long term the best solution is for me to talk folks out of using Access and getting their data into a real database. But in the mean time, there is a lot of Access data floating around, which I would like to be able to report on.
05-23-2012 08:32 AM
Have the user's export the MS Access tables to an excel spreadsheet or even a csv file format. Then create a simple upload form using a sas stored process that would allow you to grab the file off of a file share somewhere. When you click the open button to search for the file, it uses your local drive and lan drive mappings to search, then just upload the data into a data set on the sas server. I have attached an example below,
/*Code for the download/upload portlet, built in Eneterprise Guide and loaded through a stored process on the Information Delivery Portal*/
ods html file='temp.htm' style=Styles.NormalPrinter;
"A title name for your simple upload portlet.";
put '<form action="http://your sas server name goes here/SASStoredProcess/do1" enctype="multipart/form-data" method="post">';
put '<Legend>Step 1. Please download the file linked below, this is the latest Forecasting Summary.</Legend>';
put '<a href="http://your sas server name goes here/SASPortal/Director?_directive=STPRun&_program=%2FShared+Data%2FSP+SWR+INFSYS+EO+Export">
Actuals & Forcasting Summary Download Link</a>';
put '<Legend>Step 2. Please browse to your revised forecasting spreadsheet and select it for upload.</Legend>';
put '<input type="hidden" name="_program" value="sbip://METASERVER/Shared Data/SP_IRGUpdate">';
put '<table border="0" cellpadding="5">';
put '<th>Choose a file to upload:</th>';
put '<td><input type="file" name="myfile"></td>';
put '<td colspan="2" align="center"><input type="submit" value="OK"></td>';
ods html close;
/*--------------------------------------- End of Code -------------------*/
/* Code to execute the upload process*/
proc import datafile="&XLSFILE"
ods html file="temp.html" style=style.normalprinter
'The first 10 Records of the xls IRG Forecasting Update Table';
proc print data=dashdata.mydata(obs=5) Style=NormalPrinter ; run; quit;
ods html close;
/*---------End of Code to Upload file----------------*/
In the first section of code, I bolded the code you would be most interested in.
Hope it works for you..
05-23-2012 09:20 AM
Thanks. Looks like I can't avoid uploading the file to the server. Bummer. Appreciate your upload code. Will play with this approach (or just write a job to upload their data into a SQL server database that the SAS server can see).
05-23-2012 12:49 PM
Yes, was hoping to do exactly that:
Not tested,but how about creating an ODBC server/library and registering the access data in SAS MC.
AFAIK (I'm new to this), since the SAS server is a linux box and the access file is sitting on a windows server, I can't create an ODBC connection pointing to the access file. That is, I don't think the SAS server can see anything sitting on our windows file servers. Believable?