Re: Upload access file to Stored Process?

Quentin · Posted 05-01-2012 09:51 AM

Hi All,

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.

Questions:

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 like the logic and capabiliies that come with the prompting structure, don't really wan't to give all that up, or have to replicate it myself, just to upload a file). I thought maybe I could just use SAS to create the standard prompt interface, then I could modify that by adding a little html. But reading a bit, quickly became confused as to how I would go about that (quickly got over my head reading about javascript, and somewhere saw that I might need to re-register code and restart servers and oh my...)

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.

Thanks,

--Q.

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.

cociotho · Posted 05-23-2012 08:32 AM

Hi Quentin,

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*/

*ProcessBody;

ods html file='temp.htm' style=Styles.NormalPrinter;
%STPBEGIN;
Title Justify=Left
"A title name for your simple upload portlet.";
data one;
file print;
put '<html>';
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 '<br>';
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 '<tr>';
put '<th>Choose a file to upload:</th>';
put '<td><input type="file" name="myfile"></td>';
put '</tr>';
put '<tr>';
put '<td colspan="2" align="center"><input type="submit" value="OK"></td>';
put '</tr>';
put '</table>';
put '</form>';
put '</html>';
;
run;

%STPEND;
ods html close;

/*--------------------------------------- End of Code -------------------*/

/* Code to execute the upload process*/

%let XLSFILE=%sysfunc(pathname(&_WEBIN_FILEREF));
*ProcessBody;
proc import datafile="&XLSFILE"
out=dashdata.mydata
dbms=xls
replace ;
getnames=yes;
run; quit;

ods html file="temp.html" style=style.normalprinter
%STPBEGIN;
Title Justify=Left
'The first 10 Records of the xls IRG Forecasting Update Table';
proc print data=dashdata.mydata(obs=5) Style=NormalPrinter ; run; quit;
%STPEND;
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..

Quentin · Posted 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).

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.

shivas · Posted 05-23-2012 12:17 PM

Hi Quentin,

Not tested,but how about creating an ODBC server/library and registering the access data in SAS MC.

Thanks,

Shiva

Quentin · Posted 05-23-2012 12:49 PM

Thanks Shiva,

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?

--Q.

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.

Upload access file to Stored Process?