BookmarkSubscribeRSS Feed
Quentin
Super User

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.

BASUG is hosting free webinars ! Next up: Art Carpenter on February 28 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
4 REPLIES 4
cociotho
Calcite | Level 5

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
Super User

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).

BASUG is hosting free webinars ! Next up: Art Carpenter on February 28 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
shivas
Pyrite | Level 9

Hi Quentin,

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

Thanks,

Shiva

Quentin
Super User

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.

BASUG is hosting free webinars ! Next up: Art Carpenter on February 28 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 2641 views
  • 0 likes
  • 3 in conversation