* Patients Example

  For select query with two tables
  in the from clause, but no where
  clause, the cartesian product of
  the table rows is returned.
  To form the join of patients and
  doctors, a where clause is needed;

options ls=70 nodate pageno=1;
proc sql;

   * Create and populate patients table;
   create table patients (name char(10), gender char(1),
      condition char(15), docid num);
   insert into patients
      values('Sally', 'F', 'Pnemonia', 4543)
      values('Michael', 'M', 'Broken Leg', 3827)
      values('Brenda', 'F', 'Eye Injury', 4543)
      values('Conner', 'M', 'Burn', 7590);
   select * from patients;

   * Create and populate doctors table;
   create table doctors (docid num, docname char(10), phone char(13));
   insert into doctors
      values(7590, 'Cheng', '222/222-2222')
      values(4543, 'Patel', '333/333-3333')
      values(3827, 'Suarez', '444/444-4444');
   select * from doctors;

   * Form cartesian product of tables;
   select name, docname from patients, doctors;

   * Return join of patients and doctors on docid;
   select name, condition, docname, phone 
      from patients, doctors
      where patients.docid = doctors.docid;

quit;