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." ... View more

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. ... View more

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

EDIT: I meant @Cynthia_sas NOT: @cynthia ... View more

@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! ... View more

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! ... View more

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

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 ... View more

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 */;   ... View more