Importing sql dump file into SAS Studio ODA

[Patrick]

@advanceddriver The easiest way to copy data from a database to SAS is by using one of the SAS Access engines. For this to work you need the access engine (like ODBC) licensed and have a connection to the database configured. 

If that's not the case then you need the SQL - and many databases have extensions to standard SQL and though often generate code that only runs on the specific database without any changes.

If you've got the Create Table SQLs from your database then you can try and run it "as is" using SAS - but there is a good chance it's not 100% SAS SQL compliant code so you will have to figure out what needs change. 

The other option is an export in json format. There you have a good chance that you can read these json's directly with SAS and that this will create the tables in SAS.

[Kurt_Bremser]

You need to write the data step in such a way that it replicates the database table structure as closely as possible. Keep in mind that SAS has only two datatypes, character and numeric, so stuff like the enumerated columns has to be mapped to something which makes sense.

E.g. a column with values Y and N is best converted to numeric 1 and 0, with a display format to show the characters (the variable can then be used directly in conditions).

Any resulting dataset can be used in PROC SQL, no matter how it was created.

[advanceddriver]

Thank you Kurt for this v useful info:-
I thanked the wrong person for your last sentence [!], so I am copying that message below:-

"Finally, thanks again for pointing out that PROC SQL can be used to query data entered in other ways [I tried out an xlsx [Excel] file]. Having said that, I did have some conflicts with data types in Excel and those in PROC SQL, and to do with referential integrity including CASCADE DELETE, but I think I can sort those."

[LinusH]

Yes, please share som snippet from the dump file.

If this is the type of dump file that contains SQL statements (CREATE TABLE and INSERT INTO), maybe your best shot is to open it in a text editor, and do find/replace on component to make compatible with SAS Proc SQL.

Another option is to load into a SQL server database that your SAS installation can reach, using SAS/ACCESS (from SAS Compute) or Data Connectors (CAS). If your database is not too big you could use SQL Server Express ed.

[advanceddriver]

Thank you, Linus H,

Please see my reply to Yabvon's post [which was the 2nd reply above to my Q] to see part of the sql code. in the dump file.

In your suggestion of "find/replace", I am not sure what to replace with what tho yes there are CREATE TABLE and INSERT INTO queries therein.
Also, as I am using SAS ODA, I am not sure how I can install e.g. a mysql server into the server-sided file structure that appears to the left side of the screen, because my version of SAS is a learning one and I am not able to get SAS to access my desktop file structure.

Thanks in advance for any clarification and/or sample code.
M

[Cynthia_sas]

Hi:
You will not be able to install a mysql server into the SAS ODA file system. The SAS OnDemand server does not provide users with that kind of access to the operating system. And, as you have discovered, the SAS OnDemand for Academics server can only work with uploaded files, it cannot read from your local machine to access your database.
Cynthia

[advanceddriver]

I appreciate your input - however, if I may enquire, partly cos of Patrick's very useful input above, and partly cos I know that SAS ODA can run PROC SQL queries using files on the server, whether SAS ODA has a running sql server of its own [whatever sql syntax it uses], and how can one use this server in conjunction with an uploaded SAS-compatible database?

Thanks!

[Cynthia_sas]

Hi:

  SAS OnDemand for Academics does not have a SQL server -- either MySQL or MS-SQL server available. PROC SQL is primarily intended for students and independent learners to learn how to use the SQL procedure, at the level that we teach in the Programming 1 and SQL 1 classes. We do NOT use any external databases in these classes.

Cynthia