Importing sql dump file into SAS Studio ODA

[advanceddriver]

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;

68

69

70

71 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

81

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!

[Patrick]

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; 

[advanceddriver]

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

[Patrick]

---

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

[yabwon]

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

Bart

[advanceddriver]

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 */;

[yabwon]

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

[Tom]

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:

[advanceddriver]

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

[Tom]

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.

[advanceddriver]

EDIT:
I meant @Cynthia_sas
NOT:
@cynthia

[advanceddriver]

Good afternoon Tom,

Just a little update after playing around a little with PROC SQL.

It does accept CONSTRAINT, but with different syntax as below [and in a different location].

[Apologies but I could not paste above screenshot into a code box].

Also, INT and INTEGER are both available in PROC SQL as in other types of SQL [refers to a data type where only a whole number can be used] [it also seems like FLOAT for a small decimal data type works in PROC SQL, tho as you rightly say, not in SAS proper [which oc has only two data types]].

ENUM is used in mysql to specify specific items to be entered when typing data for that column/attribute [almost like an input m,ask], and you are right again: it is not part of PROC SQL. 

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.

I hope our chat will help other members incl newbies to SAS like myself, and to those learning sql.

[Tom]

Some points.

First, to me SQL is a QUERY language.  Its part of its name after all.

So if you are teaching someone new then teach them how to use SQL to actually query data and get the results they need.

The other auxiliary stuff link CREATE TABLE that DBMS had to invent so they that could do database management stuff is not really of any importance to actual querying.  SAS already had tools for building datasets before SQL was even out of the professor's laboratories.  So there is really not much need to use SQL code to create datasets or load data.  Just use normal SAS code for that.   

But as you noticed PROC SQL will try to comply with value ANSI 1999 SQL syntax.  So it honors types like INTEGER (or its alias INT), but it just goes ahead and makes a normal SAS numeric variable.  So it is useful if you already have someone's SQL to make the table you don't have to re-create the code.  But if you just want to make a dataset is is much simpler to just write a data step.

Things like constraints might be implemented in SAS but it is not something I would use very often there.  If I had to build some type of transactional database system to track data where constraints might have some utility I would do it in a database management system and then just connect to it with my SAS code when I needed to analyze the data it collected.

[Kurt_Bremser]

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.

[advanceddriver]

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