05-08-2014 09:30 PM
Without a dedicated administrator, I have been left to my own devices to try to fix an issue I am having when trying to connect to a MySQL database. I have set up the connection through the SAS Management Console, and made it comparable to existing connections that work (however they are for other departments therefore I shouldn't use them).
The issue I have is that when I try to assign my libname I get the error
"ERROR: Error trying to establish connection: Unknown database"
I would have hoped it would create the SQL database if it didn't exist, but that doesn't seem to be the case. My question is, how do I create the database and can I do it through the Management Console (similar to creating a new file, folder or process with a right click?) I don't have access to the server itself, so can't just log on and do what I need to do.
Oh and please excuse my ignorance, I am a programmer/analyst - not an Administrator (but can follow instructions if someone knows how to achieve what I am trying to do!)
05-08-2014 10:17 PM
If the MySQL database does not exist you need to log into MySQL as an administrator and create the database. Once the database is created you can use SQL passthrough from SAS to create tables and the like.
05-12-2014 08:58 PM
Thanks Richard. I figured the guy that originally set it up for me would have done that. But alas, no (his care factor at the moment is non existent). Hopefully I can pry the mysql
information out of him before he leaves at the end of the week.
05-12-2014 09:22 PM
The most important thing is to ensure that this guy has responsibly passed on administrator account details and passwords. Speak to his boss if necessary. You can always get MySQL doco off the web.
05-12-2014 11:58 PM
It occurs to me to wonder why you are trying to create MySQL tables if you are, as it seems, to be the sole user. It would make more sense to store your data in permanent SAS libraries, so avoiding all the hassles you are experiencing.
05-13-2014 12:10 AM
Hi Richard - they are stored in permanent SAS libraries, however I need to connect them to Tableau and I've been told that Tableau doesn't support SAS directly very well, hence the conversion into MySQL.
Happy to listen to any other suggestions you may have
05-13-2014 01:32 AM
Tableau will import from MS Access which you probably already have on your desktop. Export to MS Access on your desktop might be more straightforward than setting up a MySQL server, given the uncertainty of admin support. Not such a good idea though if your tables are very large - not sure what the current limit on MS Access is.
05-13-2014 01:38 AM
The problem I have is that I am using SAS on my desktop, connecting to a Linux server so I don't actually have any physical connection (such as directory mapping) to the server direct. I am old school and prefer to write my own code rather than use the file, export point and click method, and because I will be running the script every month, I find it easier to hard code what I want to do.
To get around it in the meantime, I managed to work out the path of the folder I had been assigned on the server and I have been converting my data to .csv format before zipping then emailing it to myself (all using SAS code). It isn't the most ideal way of doing it, but with the limited support and resources here, I managed to find a way to at least give me some sort of result!
05-13-2014 02:23 AM
@ddeaves, You are lost in opinions and habits. Your SAS session is running on Linux that is not really different as Windows see that as your running environment instead of your laptop.
Bill-Gates implemented a lot in Windows like Unix, but some things are to be translated like:
- the \ (Windows backslash) into / (Unix forward slash) and the "my documents" as a folder like "\home\ddeaces" in shortkey "~".
- filenames being case-insensitive (Windows) into a case-sensitive approach (Unix). Advice: use lower-cases.
The Eguide usage is really for programmers and used that way offering more as the DMS. That some sales-person did propagate other expectations is not smart and not correct.
http://support.sas.com/resources/papers/proceedings13/138-2013.pdf is a nice starter for a oldy. I think sometimes getting gray hairs of all those being block-mindeed.
Getting your data transferred:
If neither of these has been realized and tested verified, you are in a lost situation. Not a technical question just a misfit of actions.
Tableau is running in a MS environment as far as I can see. It is supporting ODBC Tableau and ODBC | Tableau Software
When your tables are not too big (performance) and you are having a SAS/Share license. You could connect SAS datasets directly with that.
SAS/Share is able to open up SAS datasets being used by ODBC. SAS ODBC Drivers
05-13-2014 03:32 AM
More than one way to skin the cat! If, as Jaap suspects, your SAS is installed on the server and you have Enterprise guide to play with, export to MS Access may still be a good option. To Quote Chris Hemedinger at SAS:
"Using SAS Enterprise Guide, you can import and export MS Access tables and Excel spreadsheets via point-and-click, and SAS/ACCESS is not required. (This method does not use/generate a SAS program — and it does not support “update-in-place” for an existing table.)"
To my mind, falling back on CSV and emails is a counsel of despair, even for an old timer.
Also an old schooler-