topic Re: Importing sql dump file into SAS Studio ODA in New SAS User

Importing sql dump file into SAS Studio ODA

advanceddriver — Wed, 06 Dec 2023 10:20:29 GMT

Good morning all - I wonder if someone could help with what I thought would be a simple task.

I am trying to read into SAS [using SAS Studio 9.4 ODA on Windows 10] a small database that I saved as a dump file on my desktop and then uploaded this dump file onto the server [hardly 5-6 tables each with max 15 rows - total file size < 200 KB] so that I can then practice using PROC SQL queries to access specific data [learning exercise] from this database. I have tried various ways but in vain. A couple of attempts are below:- [thanks in advance!]

Attempt No. ONE.

caslib mycaslib desc='MySQL Caslib'  
     dataSource=(srctype='mysql',           
                 host='localhost' 
                 username='root',           
                 password='[******]',          
                 database='cardiotech', 
                 schema='mySchema'); 
                  
RUN;

Log when I ran above code:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

71 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Attempt No. TWO:

data _null_;  
  file "/home/myusername/sasuser.v94/SQL/bdump.sql"; 
  put 'select * from sashelp.class;'; 
run; 
options source2; 
proc sql; 
 
%include "/home/myusername/sasuser.v94/SQL/bdump.sql"; 
SELECT * FROM patients, chest_pain 
WHERE patient.patient_id = chest_pain.patient_id; 
 
RUN;

Error message when I run No.2 code above:-

78 SELECT * FROM patients, chest_pain

79 WHERE patient.patient_id = chest_pain.patient_id;

ERROR: File WORK.PATIENTS.DATA does not exist.

ERROR: File WORK.CHEST_PAIN.DATA does not exist.

Apologies if I an missing something v empirical, but I hope this Q sparks some interest/enthusiasm, and that someone can find a few minutes to help out!

Tysm again!

Re: Importing sql dump file into SAS Studio ODA

Patrick — Wed, 06 Dec 2023 12:52:51 GMT

Your first attempt can't work because a caslib requires the SAS Viya version plus is for SAS CAS in-memory tables.

Your 2nd attempt is much closer. The following code works

%let path=%sysfunc(pathname(work));
data _null_;  
  file "&path/bdump.sql"; 
  put 'select * from sashelp.class;'; 
run; 
options source2; 
proc sql; 
   %include "&path/bdump.sql"; 
RUN;

A SQL Server dump file is for backup and import into SQL Server. You also couldn't use it for any other database like Oracle etc.

If you want to use it for SAS SQL then you need to parse the dump file and amend the syntax so it's suitable for SAS Proc SQL.

You're on the right path but your data _null_ step needs to modify the dump file syntax and write SAS SQL syntax to the file which you use as %include. Something along the line of below:

%let path=%sysfunc(pathname(work));
data _null_;  
/*  file print;*/
  file "&path/sas_syntax.sql"; 

  infile "/home/myusername/sasuser.v94/SQL/bdump.sql";
  input;
  /* here code that changes the string in _infile_ so it's good for SAS SQL */
  /* ..... */

  /* and now write the modified code to file */
  put _infile_; 
run;

Re: Importing sql dump file into SAS Studio ODA

yabwon — Wed, 06 Dec 2023 13:37:27 GMT

Can you share some example of the dump file ? (or create such example with anonimised data)

Bart

Re: Importing sql dump file into SAS Studio ODA

LinusH — Thu, 07 Dec 2023 14:54:59 GMT

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.

Re: Importing sql dump file into SAS Studio ODA

advanceddriver — Thu, 07 Dec 2023 19:19:15 GMT

Hi there - thank you for your reply and request.

Please see below part of the sql code from the dump file [I used mysql to create this small database]:

[I did try to use the second snippet of code provided in the first reply; it ran and did not give any errors so I assume that it worked i.e. that it modified the mysql dump file to convert it into SAS compatible sql [??].

However, I failed in trying to subsequently import or use that file [I tried the IMPORT wizard, which gave an error that it did not recognize the DBMS type "mysql" OR "sql" - maybe I need to use a different DBMS type [??]]

I shall try and upload the code of my attempts as above in reply to the first reply to my Q.

-- MySQL dump 10.13 Distrib 5.5.35, for Win64 (x86)
--
-- Host: localhost Database: cardiotech
-- ------------------------------------------------------
-- Server version 5.5.35

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `ankle_swelling`
--

DROP TABLE IF EXISTS `ankle_swelling`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ankle_swelling` (
`type_id` int(11) DEFAULT NULL,
`severity` varchar(20) NOT NULL,
`swelling_time` int(11) NOT NULL,
`patient_id` int(11) DEFAULT NULL,
UNIQUE KEY `type_id` (`type_id`),
KEY `swellingFK` (`patient_id`),
CONSTRAINT `swellingFK` FOREIGN KEY (`patient_id`) REFERENCES `patient` (`patient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `ankle_swelling`
--

LOCK TABLES `ankle_swelling` WRITE;
/*!40000 ALTER TABLE `ankle_swelling` DISABLE KEYS */;
/*!40000 ALTER TABLE `ankle_swelling` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `auscultation`
--

DROP TABLE IF EXISTS `auscultation`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `auscultation` (
`FirstSound` enum('Y','N') NOT NULL,
`SecondSound` enum('Y','N') NOT NULL,
`mitralclickpresent` enum('Present','Absent') NOT NULL,
`Auscultation_id` int(1) NOT NULL,
`ChestSign_id` int(1) DEFAULT NULL,
`Murmur` enum('Y','N') NOT NULL,
UNIQUE KEY `Auscultation_id` (`Auscultation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `auscultation`
--

LOCK TABLES `auscultation` WRITE;
/*!40000 ALTER TABLE `auscultation` DISABLE KEYS */;
/*!40000 ALTER TABLE `auscultation` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `chest_pain`
--

DROP TABLE IF EXISTS `chest_pain`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `chest_pain` (
`pain_id` int(2) NOT NULL AUTO_INCREMENT,
`pain_type` varchar(30) NOT NULL DEFAULT 'unknown',
`pain_site` varchar(20) NOT NULL,
`pain_freq` enum('Once','Twice','Thrice','Several times') DEFAULT NULL,
`pain_severity` int(2) NOT NULL,
`pain_atrest_or_onexertion` enum('At Rest','On exertion') NOT NULL,
`pain_radiation` enum('none','left arm','right arm','jaw','back','neck') NOT NULL,
`patient_id` int(11) DEFAULT NULL,
`symptom_id` int(11) DEFAULT NULL,
`pain_per` enum('Minute','Hour','Day','Week') DEFAULT NULL,
PRIMARY KEY (`pain_id`),
UNIQUE KEY `pain_id` (`pain_id`),
KEY `painFK` (`patient_id`),
CONSTRAINT `painFK` FOREIGN KEY (`patient_id`) REFERENCES `patient` (`patient_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `chest_pain`
--

LOCK TABLES `chest_pain` WRITE;
/*!40000 ALTER TABLE `chest_pain` DISABLE KEYS */;
INSERT INTO `chest_pain` VALUES (3,'tight','retro-sternal','Twice',5,'On exertion','left arm',23,2,'Week'),(4,'tight','central','Twice',6,'At Rest','left arm',27,0,'Day'),(5,'Crushing','central','Thrice',8,'At Rest','left arm',28,0,'Week'),(6,'Crushing','central','Thrice',7,'On exertion','right arm',31,0,'Week'),(7,'Crushing','left precordial','Thrice',7,'On exertion','none',35,0,'Week'),(8,'Crushing','retro-sternal','Thrice',6,'On exertion','jaw',38,0,'Week'),(9,'Crushing','retro-sternal','Thrice',6,'On exertion','jaw',39,0,'Week'),(10,'Crushing','retro-sternal','Thrice',6,'At Rest','jaw',40,0,'Week'),(11,'Crushing','retro-sternal','Thrice',6,'At Rest','back',42,0,'Week'),(12,'Crushing','retro-sternal','Twice',6,'On exertion','back',43,0,'Week'),(13,'stabbing','retro-sternal','Twice',6,'On exertion','jaw',45,0,'Week');
/*!40000 ALTER TABLE `chest_pain` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `chestsign`
--

DROP TABLE IF EXISTS `chestsign`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `chestsign` (
`palpation` varchar(255) DEFAULT NULL,
`percussion` enum('normal','abnormal') DEFAULT NULL,
`auscultation` varchar(6) DEFAULT NULL,
`bp_systolic` int(3) DEFAULT NULL,
`bp_diastolic` int(3) DEFAULT NULL,
`sign_id` int(11) DEFAULT NULL,
`ChestSign_id` int(11) NOT NULL AUTO_INCREMENT,
`heart_rate` int(3) NOT NULL,
`sternal_heave` enum('present','absent') DEFAULT NULL,
`patient_id` int(5) DEFAULT NULL,
`pulse_type` enum('Normal','Dicrotic','Collapsing','Bisferiens') DEFAULT NULL,
`JVP` int(1) DEFAULT NULL,
UNIQUE KEY `ChestSign_id` (`ChestSign_id`),
UNIQUE KEY `ChestSign_id_2` (`ChestSign_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `chestsign`
--

LOCK TABLES `chestsign` WRITE;
/*!40000 ALTER TABLE `chestsign` DISABLE KEYS */;
INSERT INTO `chestsign` VALUES (NULL,'normal',NULL,145,87,NULL,1,84,'present',23,'Collapsing',2),(NULL,'normal',NULL,135,76,NULL,2,74,'present',27,'Bisferiens',2),(NULL,'normal',NULL,115,65,NULL,3,59,'absent',28,'Bisferiens',2),(NULL,'normal',NULL,123,69,NULL,4,76,'absent',31,'Normal',3),(NULL,'normal',NULL,145,87,NULL,5,73,'absent',35,'Bisferiens',1),(NULL,'normal',NULL,165,98,NULL,6,93,'absent',38,'Dicrotic',4),(NULL,'normal',NULL,165,88,NULL,7,93,NULL,39,'Dicrotic',4),(NULL,'normal',NULL,139,75,NULL,8,83,'absent',40,'Collapsing',NULL),(NULL,'normal',NULL,136,78,NULL,9,94,'present',42,'Dicrotic',NULL);
/*!40000 ALTER TABLE `chestsign` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `dizziness`
--

DROP TABLE IF EXISTS `dizziness`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `dizziness` (
`dizzy_postural` enum('Postural','Non-postural') NOT NULL,
`dizzy_syncope` enum('Yes','No') DEFAULT NULL,
`dizzy_bp_systolic` int(3) NOT NULL,
`dizzy_bp_diastolic` int(3) NOT NULL,
`patient_id` int(11) DEFAULT NULL,
`apex_rate` int(3) DEFAULT NULL,
`symptom_id` int(5) DEFAULT NULL,
KEY `dizzyFK` (`patient_id`),
CONSTRAINT `dizzyFK` FOREIGN KEY (`patient_id`) REFERENCES `patient` (`patient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `dizziness`
--

LOCK TABLES `dizziness` WRITE;
/*!40000 ALTER TABLE `dizziness` DISABLE KEYS */;
INSERT INTO `dizziness` VALUES ('Non-postural','Yes',102,86,27,NULL,NULL),('Postural','No',124,78,23,NULL,NULL),('Non-postural','No',144,78,27,NULL,NULL),('Non-postural','No',173,108,28,NULL,NULL),('Postural','No',173,108,31,NULL,NULL),('Postural','No',133,85,35,NULL,NULL),('Postural','Yes',103,65,38,NULL,NULL),('Postural','Yes',98,69,39,NULL,NULL),('Postural','Yes',98,69,39,NULL,NULL),('Postural','No',105,58,40,NULL,NULL),('Postural','Yes',111,61,42,NULL,NULL),('Postural','Yes',111,61,42,NULL,NULL),('Postural','Yes',113,72,43,NULL,NULL),('Postural','Yes',107,64,45,NULL,NULL);
/*!40000 ALTER TABLE `dizziness` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `dyspnoea`
--

DROP TABLE IF EXISTS `dyspnoea`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `dyspnoea` (
`dyspnoea_complex` enum('Yes','No') NOT NULL,
`dyspnoea_type` varchar(20) NOT NULL,
`dyspnoea_timing` enum('Inspiratory','Expiratory') DEFAULT NULL,
`dyspnoea_grade` int(1) DEFAULT NULL,
`patient_id` int(11) DEFAULT NULL,
KEY `dyspnoeaFK` (`patient_id`),
CONSTRAINT `dyspnoeaFK` FOREIGN KEY (`patient_id`) REFERENCES `patient` (`patient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `dyspnoea`
--

LOCK TABLES `dyspnoea` WRITE;
/*!40000 ALTER TABLE `dyspnoea` DISABLE KEYS */;
/*!40000 ALTER TABLE `dyspnoea` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `ecg`
--

DROP TABLE IF EXISTS `ecg`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ecg` (
`P_wave` enum('Normal','Tall','Wide','Bifid') NOT NULL,
`PR_interval` enum('Normal','<200ms','>400ms') NOT NULL,
`QRS_complex` enum('Normal','Tall','Short','M-shaped','Negative') DEFAULT NULL,
`ST_segment` enum('Normal','Elevated','Depressed') DEFAULT NULL,
`T_wave` enum('Normal','Inverted','Tall','Bifurcated') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `ecg`
--

LOCK TABLES `ecg` WRITE;
/*!40000 ALTER TABLE `ecg` DISABLE KEYS */;
/*!40000 ALTER TABLE `ecg` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `episode`
--

DROP TABLE IF EXISTS `episode`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `episode` (
`episode_id` int(11) NOT NULL,
`episode_date` date DEFAULT NULL,
`symptom_id` int(2) DEFAULT NULL,
`sign_id` int(2) DEFAULT NULL,
UNIQUE KEY `episode_id` (`episode_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

Re: Importing sql dump file into SAS Studio ODA

advanceddriver — Thu, 07 Dec 2023 19:29:44 GMT

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

Re: Importing sql dump file into SAS Studio ODA

Cynthia_sas — Thu, 07 Dec 2023 19:53:20 GMT

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

Re: Importing sql dump file into SAS Studio ODA

advanceddriver — Thu, 07 Dec 2023 20:06:37 GMT

Thank you Patrick,
I ran the second snippet of code in your reply - it worked or at least it did s-t without giving any errors.
I presume that it modified the dump file into SAS compatible sql. [I had originally created the database using mysql].

I have tried a couple of ways to use this modified sql file but in vain.
I shall try and upload some code of my attempts here later [I have run out of steam for today!!].

[Just suffice it to say that I am not sure where this new file is located tho it seems the new file would simply have overwritten the originsal dump file].

Re: Importing sql dump file into SAS Studio ODA

advanceddriver — Thu, 07 Dec 2023 20:16:09 GMT

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!

Re: Importing sql dump file into SAS Studio ODA

yabwon — Thu, 07 Dec 2023 20:16:39 GMT

Ok, thanks for the snippet. Form what I can see in the dump file it wont be easy to import it to SAS in the for it is, MySQL's syntax is too different then one used by SAS.

SAS "import wizard" is dedicated for plain text files(CSVs) or Excels not for database dumps.

You wrote you did the dump yourself, have you considered dumping data to files in CSV format, which is "easily" importable into SAS ?

Bart

Re: Importing sql dump file into SAS Studio ODA

Tom — Thu, 07 Dec 2023 20:19:44 GMT

Most of that is gibberish to SAS (and to me).

Let's look at one of the table creation blocks. (Notice how I used the Insert Code button to paste the code into a text box.)

--
-- Table structure for table `auscultation`
--

DROP TABLE IF EXISTS `auscultation`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `auscultation` (
`FirstSound` enum('Y','N') NOT NULL,
`SecondSound` enum('Y','N') NOT NULL,
`mitralclickpresent` enum('Present','Absent') NOT NULL,
`Auscultation_id` int(1) NOT NULL,
`ChestSign_id` int(1) DEFAULT NULL,
`Murmur` enum('Y','N') NOT NULL,
UNIQUE KEY `Auscultation_id` (`Auscultation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

First thing I notice is those lines that start with two dashes. I assume those a supposed to be comments of some type? Perhaps dreaded end-of-line comments? You will need to remove those or actually convert them into comments.

Next is that DROP TABLE IF syntax. That is not valid SAS syntax. I would just comment that out also. SAS does not care if you try to recreate an existing table. It will happily blow away the existing one.

Then you have some actual comments that perhaps have some special meaning to SQL server because of the ! right after the /* ? They also have extra semicolon after the comment. Neither the comment nor the extra semicolon will cause any trouble.

Finally we get to the actual CREATE TABLE statement.

The first thing I notice is the use of backticks. Not sure what flavor of SQL uses that. If you set the DQUOTE=ANSI option on the PROC SQL statement and then convert those backticks into double quotes instead the code might work.

Then you have that enum() thingy. I would assume that they are trying to show the list of possible values? You should comment that out and instead put in an actual variable TYPE there. You would need to look at some of the actual DATA to know what they meant by that. Did they mean that it should store 1 and 2 and display them as Y and N? Did they mean it should store Y and N and just complain if you tried to store some other letter? Whether they want to store the display values or some code value will determine if those should be numeric variables or character variables.

I have no idea what int(1) means as a type either. But things are much easier in SAS. You only have two types to worry about. Numbers (which are 64-bit binary floating point values) and fixed length character strings. I would assume you will want to define int(1) as num.

You also have those NOT NULL and DEFAULT NULL constraints. Again those are generally useless in a SAS dataset. NOT NULL SAS will accept. DEFAULT NULL it does not. And that is what SAS does anyway so comment it out.

Then there is that UNIQUE KEY ? I assume that is somehow saying the following variable should be the key? I would assume you would want to convert that into code to actually create an index. You will need to make sure and remove the comma separator before it, which they inconveniently placed at the end of the line before instead of at the beginning of this line.

And that stuff after the end of the variable list does not really translate.

So we end up with:

proc sql dquote=ansi;
/*--*/
/*-- Table structure for table "auscultation"*/
/*--*/

/*DROP TABLE IF EXISTS "auscultation";*/
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE "auscultation" (
"FirstSound" char(1) /*enum('Y','N')*/ NOT NULL,
"SecondSound" char(1) /*enum('Y','N')*/ NOT NULL,
"mitralclickpresent" char(7) /*enum('Present','Absent')*/ NOT NULL,
"Auscultation_id" num /*int(1)*/ NOT NULL,
"ChestSign_id" num /*int(1)*/ /*DEFAULT NULL*/,
"Murmur" char(1) /*enum('Y','N')*/ NOT NULL/*,*/
/*UNIQUE KEY "Auscultation_id" ("Auscultation_id")*/
) /*ENGINE=InnoDB DEFAULT CHARSET=latin1*/;
/*!40101 SET character_set_client = @saved_cs_client */;
create unique index  "Auscultation_id" on "auscultation"("Auscultation_id");
quit;

Which produces dataset like this:

Re: Importing sql dump file into SAS Studio ODA

Cynthia_sas — Thu, 07 Dec 2023 20:40:14 GMT

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

Re: Importing sql dump file into SAS Studio ODA

Patrick — Thu, 07 Dec 2023 22:04:26 GMT

@advanceddriver wrote:
Thank you Patrick,
I ran the second snippet of code in your reply - it worked or at least it did s-t without giving any errors.
I presume that it modified the dump file into SAS compatible sql. [I had originally created the database using mysql].
....

Hi @advanceddriver

In the sample code there was this bit where you would need to write code that modifies the text you read from your source file so it becomes SAS syntax.

Looking into the MySQL docu it appears you can also export to json files.

https://dev.mysql.com/doc/workbench/en/wb-admin-export-import.html

https://dev.mysql.com/doc/workbench/en/wb-admin-export-import-table.html

If the mysql export wizard gives you the option then exclude all constraints. You don't need them for your purpose.

Reading such a json file into SAS would likely be more straightforward. SAS got a json engine and you'll find sample code how to read a json using SAS.

I suggest you give it a go and if you get stuck reading the json into SAS tables then share the full json for one table here as attachment.

Re: Importing sql dump file into SAS Studio ODA

Kurt_Bremser — Sat, 09 Dec 2023 21:34:01 GMT

See here for how to export MySQL data to a text file, which can easily be read with a DATA step.

In the end, writing that DATA step will be (much) faster than converting the dumpfile to SAS SQL code.

Re: Importing sql dump file into SAS Studio ODA

advanceddriver — Sat, 09 Dec 2023 21:02:31 GMT

@Tom

Thank you for this detailed info - in my experience, SAS sql is not v different from e.g. mysql or postgres sql - I think the "gibberish" is cos my data was copied from a dump file, in which some inbuilt mechanism has created e.g. the double dashes.

I have my original mysql code for the whole database, which appears to be v similar to SAS sql as in the code you have provided.

My residual problem is now that SAS does not accept "CREATE DATABASE" or "USE DATABASE" [which is standard sql format in e.g. mysql], so [and this will sound so simple to you I am sure], but if I create all my tables and insert all the data [for which I have all the "INSERT INTO" code lines, how do I make them all reside in a single [named] database? I need to subsequently run sql SELECT queries using PROC SQL with multiple tables with specific relationship types e.g. I need to use INNER JOIN, CROSS JOIN, etc. The member named @cynthia is, as far as I can see, a business person with little techniocal knowledge cos she just points out what cannot be done rather than helping with what can, bordering on a sales pitch for the full package. For instance, her theory is that PROC SQL can handle only basic queries, WHEREAS it is clear from the logs and from the tooltips within PROC SQL in SAS ODA that the system can handle various types of joins as well as run queries from multiple tables.

I wonder if you would be able to help out with this task i.e. to create and save several tables to a single database/file so that I can run queries using multiple tables.

Thanks again in advance, Tom!

Re: Importing sql dump file into SAS Studio ODA

Tom — Sat, 09 Dec 2023 21:09:12 GMT

In SAS a single level dataset name NORMALLY refers to a dataset in the WORK library.

So you could just make the datasets in WORK and and then copy them to some other library.

libname mylib 'mydirectory name';
proc sql;
%include 'mycode.sql'/source2;
quit;
proc copy inlib=work outlib=mylib;
run;

But it also has the USER setting that will cause single level dataset name to refer to a different libref instead.

The original and simplest way is to define a libref named USER and then SAS will automatically use that.

libname user 'mydirectory name';
proc sql;
%include 'mycode.sql') / source2;
quit;
libname user clear;
libname mylib 'mydirectory name';

There are now more complex ways using the USER system option.

Re: Importing sql dump file into SAS Studio ODA

advanceddriver — Sat, 09 Dec 2023 21:13:02 GMT

EDIT:
I meant @Cynthia_sas
NOT:
@cynthia

Re: Importing sql dump file into SAS Studio ODA

advanceddriver — Sun, 10 Dec 2023 11:42:31 GMT

@Kurt_Bremser

Thank you for this suggestion, Kurt. I might try it out tho my gut feeling is that the file will be read by SAS in a format other than what I can run SQL queries on [even if I save the file as a .csv, which another member has suggested]; my whole aim of this exercise is to run sql queries within SAS ODA [using PROC SQL] cos I am helping a "student" who needs to learn to do just that.

In his work setting, his employers have provided him with the full SAS desktop package, so later there may not be an issue in reading [actually quite large and diverse] databases into SAS - it is during the current learning period that I need to achieve whatever is possible/permissible in SAS ODA.

Re: Importing sql dump file into SAS Studio ODA

Patrick — Sun, 10 Dec 2023 12:02:13 GMT

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

Re: Importing sql dump file into SAS Studio ODA

Kurt_Bremser — Mon, 11 Dec 2023 08:16:43 GMT

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.