* Kids4 Example
  Perform several database using SQL;

option nodate ls=80 pageno=1;
data kids;
   infile "c:/datasets/kids.txt" firstobs=2;
   length name $ 15;
   input name $ gender $ age;
   
* Display beginning of kids dataset;
proc print data=kids (obs=20);
  
proc sql;

   title "Show girls only";
   select name, age 
      from kids
      where gender="F";

   title "Compute mean age by gender";
   select gender, mean(age) as ave_age 
      from kids
      group by gender;

* Assign Doctors randomly to patients
  This "table" probability distribution assigns
  numbers 1 through 5 randomly with probability 1/5;
data kids2;
   set kids;
   rannum = rand("table", 1/5, 1/5, 1/5, 1/5, 1/5);
   select(rannum);
      when (1) prof_id = 328;
      when (2) prof_id = 517;
      when (3) prof_id = 694;
      when (4) prof_id = 798;
      when (5) prof_id = 803;
   end;
   drop rannum;

proc print data=kids2;

data teachers;
   input teacher_id teacher_name $ @@;
   datalines;
328 Rao     517 Zapata  694 Chen
798 Murphy  803 Nkosi
;

proc print data=teachers;
run;

proc sql;
   title "Kids name with teachers name";
   select kids2.name, teachers.teacher_name
      from kids2, teachers
      where kids2.prof_id = teachers.teacher_id
      order by kids2.name;

quit;