Converting Access to SAS

[tomrvincent]

I'm doing a presentation at the company SAS users group meeting next month on converting Access to SAS, covering how to 'bulk convert' queries, formulas, syntax and so on.  Anyone else have dozens of Access users who are interesting in migrating to SAS?

[art297]

I'll be interested in how others answer your question. In my own experience, Access was used (and continued to be used) as a DBMS .. which SAS isn't! With SAS/Access to PC File Formats it was always easy to bring Access tables into SAS, and then use SAS to clean, analyze and report on the tables that were maintained in Access. However, I've never been anywhere where an attempt was made to "replace" Access with SAS.

Art, CEO, AnalystFinder.com

[tomrvincent]

I'd disagree with your statement saying SAS isn't a DBMS, but my mine goal is to help get rid of Access as a desktop-bound ETL tool.  We have hundreds of Access packages that read in (and duplicate) data from outside databases, do some transformations, joins, aggregations and so on...then often spits files out to CSVs or XLSs for 'final polishing' in Excel.  I've been able to convert an Access app that ran in 11 hours..ran in 15 minutes in SAS.  Hard to beat that for bang-for-the-buck.

[PaigeMiller]

You are converting the application, and not the storage of the data in an Access database table? Your problem statement isn't particularly clear on this point.

[tomrvincent]

I didn't state a problem...I posed a question.  The data is all originating in Sybase and Oracle so there's not need to convert any Access data.  Many of our users are using Access more as an ETL tool...collecting, merging, aggregating...output is almost always going out to Excel files rather than having data-entry forms and reports...YMMV.

[Reeza]

I've transferred Access DB to SAS programs. 

It's usually much faster and cleaner but you do lose some things:

1. More people have Access than have SAS

2. Access does a good job at having user interfaces so if you need user input Base SAS isn't as good. I'm liking the SAS Studio Custom Tasks though. 

I'm curious as to how bulk imported queries though, AFAIK, there isn't a one to one mapping for Access queries. Especially if pivots were used. And sometimes there's also VB code in there so it can be a complex process. 

IMO, SAS is most definitely not a DBMS. In fact, I find it works best when used in conjunction with one, MS SQL is nice for that as well. You can get power of a server without a SAS Sever installation. 

[ballardw]

I would start by using the Access documentation tool, at least the one I remember which may no longer there, that created a reports of all of the tables queries and such. You wouldn't want most of the properties though.

IIRC that text should be somewhat amenable to read and create separate SAS program files for many pieces using that information.

Most of the query syntax should clean up relatively quickly. The table definitions only need a few pieces: is a field text or not? If numeric and not a date, time or date time SAS only has one storage definition so all of the int float bigint or whatever go away.

Take a look at some output of the documenter and see for yourself what is there.

Anything VBA based would be a crapshoot for conversion.

The forms for data entry may not be terribly useful .

The Report definitions would probably be better done from scratch with appropriate Proc Report, Tabulate or Print rather than trying to turn convert. You might find some stuff useful for Informats or formats though.