BookmarkSubscribeRSS Feed
louisehadden
Quartz | Level 8

Hi, all. We have been running a (somewhat antiquated) program to create an ACCESS db with multiple tables uneventfully for many years. Our company migrated our servers to Office 365 instead of standalone copies of Access, Word, etc. recently and the program no longer works. We get a connection error. We've determined that it is a connection error. Does anyone have any handy tips on (1) setting up an OBDC connection for SAS->MS Access 365 and (2) any tweaks required to the PROC EXPORT code? Any other suggestions? This file is generated every month and we definitely don't have time for tweaking for this month's client delivery - we know we need to modernize in the near future but need a quick fix for now. Any suggestions on how to fill in the blanks programmatically (i.e. syntax for the OBDC connection required) welcome!

louisehadden_0-1641944242381.png

Thanks in advance!

4 REPLIES 4
SASKiwi
PROC Star

It would help if you posted the SAS log for your existing code so we have a clearer idea of what is happening.

ChrisHemedinger
Community Manager

Hi Louise,

 

Is the Access DB on the local machine or accessible network drive/path? Or is it legitimately "in the cloud" now that you're on Microsoft 365?

 

Assuming SAS for Windows 64-bit, see if you can test this ODBC connection using the ODBC Data Sources app that's built into Windows.

 

ChrisHemedinger_0-1641990238779.png

If that works, then the same settings should work in SAS. You can even define a System DSN to use as shorthand in your SAS code for the next time you run this.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
louisehadden
Quartz | Level 8

Thanks, Chris, I will check out the ODBC connections. We have a set up with Windows x64 Amazon servers (so data in the cloud), and previously had non-cloud 64 bit Microsoft products in place. We just need to figure out how to make the connections work in a monthly process where the output file name will differ each month. You've given me some good clues toward making this process "push button" again.

louisehadden
Quartz | Level 8

Interestingly, after trying the access export with SAS Studio, EG, Display Manager and batch SAS, getting different error messages with all 4 systems, the problem turned out to be an overlong path name, and had nothing to do with PROC EXPORT and Office 365 (except that perhaps SAS or MS Excel suddenly got picky about the length after many years of running the same program with the same path name, or that maybe our servers naming conventions changed slightly - I'm looking into that). I will put together a package for Tech Support because it really was interesting that the error messages in the logs did not ever indicate what the problem really was! A fun little debugging project for a cold winter's day.

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 634 views
  • 0 likes
  • 3 in conversation