/********************************************************************
   SAS SQL Procedure User's Guide, Version 8                         
   Publication book code:   56936                                    
                                                                   
   This file contains the source code from every example in the book 
   for which output is shown. The examples are keyed to the 
   output number.                              
                                                                   
   Unless specified otherwise, all examples assume                   
   the following settings:                                            
                                                                   
      libname sql 'SAS-data-library';                                   
      options ls=80 ps=60 nodate nonumber;                                       
                                                                     
   where the SAS-data-library refers to the location of the example 
   data files.  

   Each example can be run independently, unless otherwise noted. 
   Some of the data sets can be downloaded as a transport file. The name
   of the transport file is B56936. Any necessary data sets that are not 
   in the transport file are recreated for each example. 

                                               
********************************************************************/

/* DOWNLOADING AND SETTING UP THE SAMPLES

1. In the Run box, type ftp.sas.com/pub/publications.
 
2. Right mouse click on B56936 and select Copy to folder. 

3. Save the file to a local directory.

4. Start a SAS session.

5. Restore the SAS binary transport file. You can use Program
   Editor to submit the code:
*/

      /* Substitute the path name of your SASUSER
         directory for 'your-SASUSER-directory-path'
         and the appropriate path name and filename
         for 'your-downloaded-file-location'        */

      libname new 'your-SASUSER-directory-path';
      filename trans 'your-downloaded-file-location';

      proc cimport library=new infile=trans;
      run;

/*
6. Assign a libref named SQL to provide access to some
   of the sample data sets.

      libname sql 'your-SASUSER-directory-path';


7. Refer to the book for additional information on working
   with the samples.



________________________________________________
SAMPLES INCLUDED IN THE B56936 TRANSPORT FILE

The data sets included in the transport file B56936 are:
continents
countries
features
oilprod
oilrsrvs
postalcodes
unitedstates
uscitycoords
worldcitycoords
worldtemps

*/

/*-------------------------------------------------------------------
   OUTPUT 1.1 

   Notes:
   If you want to list the entire table, remove the OUTOBS= option.
-------------------------------------------------------------------*/
options nodate nonumber linesize=84 pagesize=60;
proc sql;
   title 'Population of Large Countries Grouped by Continent';
   select Continent, sum(Population) as TotPop format=comma15.
      from sql.countries
      where Population gt 1000000
      group by Continent
      order by TotPop;
quit;

/*-------------------------------------------------------------------
   OUTPUT 1.2 

   Notes:
   If you want to list the entire table, remove the OUTOBS= option.
-------------------------------------------------------------------*/

title 'Large Countries Grouped by Continent';
proc summary data=sql.countries;
   where Population > 1000000;
   class Continent;
   var Population;
   output out=sumPop sum=TotPop;
run;

proc sort data=SumPop;
   by totPop;
run;

proc print data=SumPop noobs;
   var Continent TotPop;
   format TotPop comma15.;
   where _type_=1;
run;

/*-------------------------------------------------------------------
   OUTPUT 1.3 

   Notes:
   If you want to list the entire table, remove the OUTOBS= option.
-------------------------------------------------------------------*/
options nodate nonumber linesize=84 pagesize=60;
proc sql outobs=15;
 title 'COUNTRIES';
 select Name format=$19., Capital format=$15.,
        Population, Area, Continent format=$15., UNDate format=year4.
    from sql.countries;

/*-------------------------------------------------------------------
   OUTPUT 1.4 

   Notes:
   If you want to list the entire table, remove the OUTOBS= option.
-------------------------------------------------------------------*/

proc sql outobs=15;
 title 'WORLDCITCOORDS';
 select City format=$15., Country format=$12., Latitude, Longitude
    from sql.worldcitycoords;

/*-------------------------------------------------------------------
   OUTPUT 1.5 

   Notes:
   If you want to list the entire table, remove the OUTOBS= option.
-------------------------------------------------------------------*/

proc sql outobs=15;
 title 'USCITYCOORDS';
 select City format=$15., State format=$2., Latitude, Longitude
    from sql.uscitycoords;

/*-------------------------------------------------------------------
   OUTPUT 1.6 

   Notes:
   If you want to list the entire table, remove the OUTOBS= option.
-------------------------------------------------------------------*/

proc sql outobs=15;
 title 'UNITEDSTATES';
 select Name format=$17., Capital format=$15.,
        Population, Area, Continent format=$13., Statehood format=date9.
    from sql.unitedstates;

/*-------------------------------------------------------------------
   OUTPUT 1.7 

   Notes:
   If you want to list the entire table, remove the OUTOBS= option.
-------------------------------------------------------------------*/

proc sql outobs=15;
title 'POSTALCODES';
 select Name , Code
    from sql.postalcodes;

/*-------------------------------------------------------------------
   OUTPUT 1.8 

   Notes:
   If you want to list the entire table, remove the OUTOBS= option.
-------------------------------------------------------------------*/

proc sql outobs=15;
title 'WORLDTEMPS';
 select City, Country,avghigh, avglow
    from sql.worldtemps;

/*-------------------------------------------------------------------
   OUTPUT 1.9 

   Notes:
   If you want to list the entire table, remove the OUTOBS= option.
-------------------------------------------------------------------*/

proc sql outobs=15;
title 'OILPROD';
 select Country, BarrelsPerDay
    from sql.oilprod;

/*-------------------------------------------------------------------
   OUTPUT 1.10 

   Notes:
   If you want to list the entire table, remove the OUTOBS= option.
-------------------------------------------------------------------*/
proc sql outobs=15;
title 'OILRSRVS';
 select Country, Barrels
  from sql.oilrsrvs;

/*-------------------------------------------------------------------
   OUTPUT 1.11 

   Notes:
   If you want to list the entire table, remove the OUTOBS= option.
-------------------------------------------------------------------*/
proc sql outobs=15;
title 'CONTINENTS';
 select Name format=$15., Area,
        Highpoint format =$15., Height, LowPoint format =$15., Depth
    from sql. continents;

/*-------------------------------------------------------------------
   OUTPUT 1.12 

   Notes:
   If you want to list the entire table, remove the OUTOBS= option.
-------------------------------------------------------------------*/
proc sql outobs=15;
title 'FEATURES';
 select Name format=$15., Type,Location format =$15.,Area,
        Height, Depth, Length
    from sql. features;

/*-------------------------------------------------------------------
   OUTPUT 2.1 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'U.S. Cities with Their States and Coordinates';
   select * 
      from sql.uscitycoords;

/*-------------------------------------------------------------------
   OUTPUT 2.2 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'Names of U.S. Cities';
   select City
      from sql.uscitycoords;

/*-------------------------------------------------------------------
   OUTPUT 2.3 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'U.S. Cities and Their States';
   select City, State
      from sql.uscitycoords;

/*-------------------------------------------------------------------
   OUTPUT 2.4 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'Continents of the United States';
   select Continent 
      from sql.unitedstates;

/*-------------------------------------------------------------------
   OUTPUT 2.5 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'Continents of the United States';
   select distinct Continent 
      from sql.unitedstates;

/*-------------------------------------------------------------------
   OUTPUT 2.6 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   describe table sql.unitedstates;

/*-------------------------------------------------------------------
   OUTPUT 2.7 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'U.S. Postal Codes';
   select 'Postal code for', Name, 'is', Code
      from sql.postalcodes;

/*-------------------------------------------------------------------
   OUTPUT 2.8 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'U.S. Postal Codes';
   select 'Postal code for', Name label='#', 'is', Code label='#'
      from sql.postalcodes;

/*-------------------------------------------------------------------
   OUTPUT 2.9 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'Low Temperatures in Celsius';
   select City, (AvgLow - 32) * 5/9 format=4.1
      from sql.worldtemps;

/*-------------------------------------------------------------------
   OUTPUT 2.10 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'Low Temperatures in Celsius';
   select City, (AvgLow - 32) * 5/9 as LowCelsius format=4.1
      from sql.worldtemps;

/*-------------------------------------------------------------------
   OUTPUT 2.11 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'Range of High and Low Temperatures in Celsius';
      select City, (AvgHigh - 32) * 5/9 as HighC format=5.1, 
                   (AvgLow - 32) * 5/9 as LowC format=5.1,
                   (calculated HighC - calculated LowC)
                    as Range format=4.1
   from sql.worldtemps;

/*-------------------------------------------------------------------
   OUTPUT 2.12 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'Climate Zones of World Cities';
   select City, Country, Latitude,
          case
             when Latitude gt 67 then 'North Frigid'
             when 67 ge Latitude ge 23 then 'North Temperate'
             when 23 gt Latitude gt -23 then 'Torrid'
             when -23 ge Latitude ge -67 then 'South Temperate'
             else 'South Frigid'
          end as ClimateZone
      from sql.worldcitycoords
      order by City;

/*-------------------------------------------------------------------
   OUTPUT 2.13 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'Assigning Regions to Continents';
   select Name, Continent,
          case Continent
             when 'North America' then 'Continental U.S.'
             when 'Oceania' then 'Pacific Islands' 
             else 'None'
          end as Region
      from sql.unitedstates;

/*-------------------------------------------------------------------
   OUTPUT 2.14 

   Notes: Either of the following two SQL statements will 
          create Output 2.14.
-------------------------------------------------------------------*/
proc sql;
   title 'Continental Low Points';
   select Name, coalesce(LowPoint, 'Not Available') as LowPoint
      from sql.continents;  

proc sql;
   title 'Continental Low Points';
   select Name, case
                   when LowPoint is missing then 'Not Available'
                   else Lowpoint
                end as LowPoint
      from sql.continents;
/*-------------------------------------------------------------------
   OUTPUT 2.15 

   Notes: 
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'Areas of U.S. States in Square Miles';
   select Name label='State', Area format=comma10. 
      from sql.unitedstates;

/*-------------------------------------------------------------------
   OUTPUT 2.16 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'Country Populations';
   select Name, Population format=comma10.
      from sql.countries
      order by Population;

/*-------------------------------------------------------------------
   OUTPUT 2.17 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'Countries, Sorted by Continent and Name';
   select Name, Continent
      from sql.countries
      order by Continent, Name;

/*-------------------------------------------------------------------
   OUTPUT 2.18 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'World Topographical Features';
   select Name, Type
      from sql.features
      order by Type desc, Name;

/*-------------------------------------------------------------------
   OUTPUT 2.19 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'World Population Densities per Square Mile';
   select Name, Population format=comma12., Area format=comma8., 
          Population/Area as Density format=comma10. 
      from sql.countries
      order by Density desc;

/*-------------------------------------------------------------------
   OUTPUT 2.20 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'World Population Densities per Square Mile';
   select Name, Population format=comma12., Area format=comma8., 
          Population/Area format=comma10. label='Density' 
      from sql.countries
      order by 4 desc;

/*-------------------------------------------------------------------
   OUTPUT 2.21 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'Countries, Sorted by Population';
   select Name, Continent 
      from sql.countries
      order by Population;

/*-------------------------------------------------------------------
   OUTPUT 2.22 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'Continents, Sorted by Low Point';
   select Name, LowPoint
      from sql.continents
      order by LowPoint;

/*-------------------------------------------------------------------
   OUTPUT 2.23 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'Countries in Europe';
   select Name, Population format=comma10.
      from sql.countries
      where Continent = 'Europe';

/*-------------------------------------------------------------------
   OUTPUT 2.24 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'States with Populations over 5,000,000';
   select Name, Population format=comma10.
      from sql.unitedstates
      where Population gt 5000000
      order by Population desc;

/*-------------------------------------------------------------------
   OUTPUT 2.25 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'Countries in Africa with Populations over 20,000,000';
   select Name, Population format=comma10. 
      from sql.countries
      where Continent = 'Africa' and Population gt 20000000
      order by Population desc;

/*-------------------------------------------------------------------
   OUTPUT 2.26 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'World Mountains and Waterfalls';
   select Name, Type, Height format=comma10.
      from sql.features
      where Type in ('Mountain', 'Waterfall')
      order by Height;

/*-------------------------------------------------------------------
   OUTPUT 2.27 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'Countries with Missing Continents';
   select Name, Continent
      from sql.countries
      where Continent is missing;

/*-------------------------------------------------------------------
   OUTPUT 2.28 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'Equatorial Cities of the World';
   select City, Country, Latitude
      from sql.worldcitycoords
      where Latitude between -5 and 5;

/*-------------------------------------------------------------------
   OUTPUT 2.29 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title1 'Country Names that Begin with the Letter "Z"';
   title2 'or Are 5 Characters Long and End with the Letter "a"';
   select Name
      from sql.countries
      where Name like 'Z%' or Name like '____a';

/*-------------------------------------------------------------------
   OUTPUT 2.30 

   Notes:
-------------------------------------------------------------------*/
/* incorrect output */

proc sql outobs=12;
   title 'World Features with a Depth of Less than 500 Feet';
   select Name, Depth
      from sql.features
      where Depth lt 500
      order by Depth;

/*-------------------------------------------------------------------
   OUTPUT 2.31 

   Notes:
-------------------------------------------------------------------*/
/* corrected output */

proc sql outobs=12;
   title 'World Features with a Depth of Less than 500 Feet';
   select Name, Depth
      from sql.features
      where Depth lt 500 and Depth is not missing
      order by Depth;

/*-------------------------------------------------------------------
   OUTPUT 2.32 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'Mean Temperatures for World Cities';
   select City, Country, mean(AvgHigh, AvgLow) 
          as MeanTemp
      from sql.worldtemps
      where calculated MeanTemp gt 75
      order by MeanTemp desc;

/*-------------------------------------------------------------------
   OUTPUT 2.33 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'World Oil Reserves';
   select sum(Barrels) format=comma18. as TotalBarrels
      from sql.oilrsrvs;

/*-------------------------------------------------------------------
   OUTPUT 2.34 

   Notes:
-------------------------------------------------------------------*/
options nodate nonumber linesize=90 pagesize=60;
proc sql outobs=12;
   title 'Largest Country Populations';
   select Name, Population format=comma20., 
          max(Population) as MaxPopulation format=comma20.
      from sql.countries
      order by Population desc;

/*-------------------------------------------------------------------
   OUTPUT 2.35 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   title 'Percentage of World Population in Countries';
   select Name, Population format=comma14.,
          (Population / sum(Population) * 100) as Percentage 
          format=comma8.2
      from sql.countries
      order by Percentage desc;

/*-------------------------------------------------------------------
   OUTPUT 2.36 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'Number of Continents in the COUNTRIES Table';
   select count(distinct Continent) as Count
      from sql.countries;

/*-------------------------------------------------------------------
   OUTPUT 2.37 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'Countries for Which a Continent is Listed';
   select count(Continent) as Count
      from sql.countries;

/*-------------------------------------------------------------------
   OUTPUT 2.38 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'Number of Countries in the SQL.COUNTRIES Table';
   select count(*) as Number
      from sql.countries;

/*-------------------------------------------------------------------
   OUTPUT 2.39 

   Notes:
-------------------------------------------------------------------*/
/* incorrect output */

proc sql;
   title 'Average Length of Angel Falls, Amazon and Nile Rivers';
   select Name, Length, avg(Length) as AvgLength 
      from sql.features
      where Name in ('Angel Falls', 'Amazon', 'Nile');

/*-------------------------------------------------------------------
   OUTPUT 2.40 

   Notes:
-------------------------------------------------------------------*/
/* corrected output */

proc sql;
   title 'Average Length of Angel Falls, Amazon and Nile Rivers';
   select Name, Length, case 
                           when Length is missing then 0
                           else Length 
                         end as NewLength, 
                avg(calculated NewLength) as AvgLength 
      from sql.features
      where Name in ('Angel Falls', 'Amazon', 'Nile');

/*-------------------------------------------------------------------
   OUTPUT 2.41 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'Total Populations of World Continents';
   select Continent, sum(Population) format=comma14. as TotalPopulation
      from sql.countries
      where Continent is not missing
      group by Continent;

/*-------------------------------------------------------------------
   OUTPUT 2.42 

   Notes:
-------------------------------------------------------------------*/
options nodate nonumber linesize=80 pagesize=60;
proc sql outobs=12;
   title 'High and Low Temperatures';
   select City, Country, AvgHigh, AvgLow
      from sql.worldtemps
      group by Country;

/*-------------------------------------------------------------------
   OUTPUT 2.44 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'Total Square Miles of Deserts and Lakes';
   select Location, Type, sum(Area) as TotalArea format=comma16.
      from sql.features
      where type in ('Desert', 'Lake')
      group by Location, Type;

/*-------------------------------------------------------------------
   OUTPUT 2.45 

   Notes:
-------------------------------------------------------------------*/
options nodate nonumber linesize=90 pagesize=60;
proc sql;
   title 'Total Square Miles of Deserts and Lakes';
   select Location, Type, sum(Area) as TotalArea format=comma16.
      from sql.features
      where type in ('Desert', 'Lake')
      group by Location, Type
      order by Location desc;

/*-------------------------------------------------------------------
   OUTPUT 2.46 

   Notes:
-------------------------------------------------------------------*/
options nodate nonumber linesize=90 pagesize=60;
/* incorrect output */

proc sql outobs=12;
   title 'Areas of World Continents';
   select Name format=$25., 
          Continent, 
          sum(Area) format=comma12. as TotalArea
      from sql.countries
      group by Continent
      order by Continent, Name;

/*-------------------------------------------------------------------
   OUTPUT 2.47 

   Notes:
-------------------------------------------------------------------*/
options nodate nonumber linesize=90 pagesize=60;
/* corrected output */

proc sql outobs=12;
   title 'Areas of World Continents';
   select Name format=$25.,
          Continent, 
          sum(Area) format=comma12. as TotalArea
      from sql.countries
      where Continent is not missing
      group by Continent
      order by Continent, Name;
/*-------------------------------------------------------------------
   OUTPUT 2.48 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'Numbers of Islands, Oceans, and Seas';
   select Type, count(*) as Number
      from sql.features
      group by Type
      having Type in ('Island', 'Ocean', 'Sea')
      order by Type;

/*-------------------------------------------------------------------
   OUTPUT 2.49 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'Total Populations of Continents with More than 15 Countries';
   select Continent, 
          sum(Population) as TotalPopulation format=comma16.,
          count(*) as Count
      from sql.countries
      group by Continent
      having count(*) gt 15
      order by Continent;

/*-------------------------------------------------------------------
   OUTPUT 2.50 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   validate
      select Name, Statehood
         from sql.unitedstates
         where Statehood lt '01Jan1800'd;

/*-------------------------------------------------------------------
   OUTPUT 2.51 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   validate
      select Name, Statehood
      from sql.unitedstates
      where lt '01Jan1800'd;

/*-------------------------------------------------------------------
   OUTPUT 3.1 

   Notes: These data steps produce the two tables that the PROC SQL
          step displays in Output 3.1.
-------------------------------------------------------------------*/
options nodate nonumber;
data one;
 input X Y $;
 datalines;
1 2
2 3
;


data two;
 input X Z $;
 datalines;
2 5
3 6
4 9
;
run;

proc sql;
  title 'Table One';
  select * from one;

  title 'Table Two';
  select * from two;

/*-------------------------------------------------------------------
   OUTPUT 3.2 

   Notes: This example uses the same datasets as in Output 3.1. The 
          data steps that create them are included here.
-------------------------------------------------------------------*/
data one;
 input X Y $;
 datalines;
1 2
2 3
;


data two;
 input X Z $;
 datalines;
2 5
3 6
4 9
;
run;

proc sql;
   title 'Table One and Table Two';
   select *
      from one, two;

/*-------------------------------------------------------------------
   OUTPUT 3.4 

   Notes: This example uses the same datasets as in Output 3.1. The 
          data steps that create them are included here.
-------------------------------------------------------------------*/
data one;
 input X Y $;
 datalines;
1 2
2 3
;


data two;
 input X Z $;
 datalines;
2 5
3 6
4 9
;
run;

proc sql;     
   select * from one, two
      where one.x=two.x;

/*-------------------------------------------------------------------
   OUTPUT 3.5 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=6;
   title 'Oil Production/Reserves of Countries';
   select p.country format $20.,
          barrelsperday,
          r.country format $20.,
          barrels
   from sql.oilprod as p, sql.oilrsrvs as r
      where p.country = r.country;

/*-------------------------------------------------------------------
   OUTPUT 3.6 

   Notes: Both of the following PROC SQL steps 
          will produce Output 3.6.
-------------------------------------------------------------------*/
proc sql outobs=6;
   title 'Oil Production/Reserves of Countries';
   select p.country, barrelsperday 'Production', barrels 'Reserves'
      from sql.oilprod p, sql.oilrsrvs r
      where p.country = r.country
      order by barrelsperday desc;

proc sql outobs=6;
   title 'Oil Production/Reserves of Countries';
   select p.country, barrelsperday 'Production', barrels 'Reserves' 
      from sql.oilprod p inner join sql.oilrsrvs r
           on p.country = r.country
      order by barrelsperday desc;

/*-------------------------------------------------------------------
   OUTPUT 3.7 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'US Cities South of Cairo, Egypt';
   select us.City, us.State, us.Latitude, world.city, world.latitude
      from sql.worldcitycoords world, sql.uscitycoords us
      where world.city = 'Cairo' and
            us.latitude lt world.latitude;

/*-------------------------------------------------------------------
   OUTPUT 3.9 

   Notes: These data steps produce the two tables that the PROC SQL
          steps display in Output 3.9. 
-------------------------------------------------------------------*/
data one;
 input a $ b;
 datalines;
 a 1
 b 2
 c .
 d 4 
 ;
  
data two;
 input a $ b;
 cards;
 a 1
 b 2
 c .
 d 4
 e .
 f .
 ;
  run;

proc sql;
   title 'Table One';
   select * from one;
   title 'Table Two';
   select * from two;

proc sql;
   title 'One and Two Joined';
   select one.a 'One', one.b, two.a 'Two', two.b
      from one, two
      where one.b=two.b;

/*-------------------------------------------------------------------
   OUTPUT 3.10 

   Notes: This example uses the same datasets as in Output 3.9. The 
          data steps that create them are included here.
-------------------------------------------------------------------*/
data one;
 input a $ b;
 datalines;
 a 1
 b 2
 c .
 d 4 
 ;
  
data two;
 input a $ b;
 cards;
 a 1
 b 2
 c .
 d 4
 e .
 f .
 ;

run;

proc sql;
   select one.a 'One', one.b, two.a 'Two', two.b
      from one, two
      where one.b=two.b and
            one.b is not missing;

/*-------------------------------------------------------------------
   OUTPUT 3.11 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'Coordinates of Capital Cities';
   select Capital format=$12., Name format=$12.,
          City format=$12., Country format=$12., 
          Latitude, Longitude
      from sql.countries, sql.worldcitycoords
      where Capital like 'L%' and
                  Capital = City;

/*-------------------------------------------------------------------
   OUTPUT 3.12 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'Coordinates of Capital Cities';
   select Capital format=$12., Name format=$12.,
          City format=$12., Country format=$12., 
          latitude, longitude
      from sql.countries, sql.worldcitycoords
      where Capital like 'L%' and
            Capital = City and
            Name = Country;

/*-------------------------------------------------------------------
   OUTPUT 3.13 

   Notes:
-------------------------------------------------------------------*/
title 'Coordinates of State Capitals';
proc sql outobs=10;
   select us.Capital format=$15., us.Name 'State' format=$15., 
          pc.Code, c.Latitude, c.Longitude
      from sql.unitedstates us, sql.postalcodes pc, 
           sql.uscitycoords c
      where us.Capital = c.City and
            us.Name = pc.Name and
            pc.Code = c.State;

/*-------------------------------------------------------------------
   OUTPUT 3.14 

   Notes:
-------------------------------------------------------------------*/
 proc sql;
    title "Cities' High Temps = Cities' Low Temps";
    select High.City format $12., High.Country format $12., 
           High.AvgHigh, ' | ',
           Low.City format $12., Low.Country format $12., 
           Low.AvgLow
       from sql.worldtemps High, sql.worldtemps Low
       where High.AvgHigh = Low.AvgLow and
             High.city ne Low.city and
             High.country ne Low.country;

/*-------------------------------------------------------------------
   OUTPUT 3.15 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=10;
   title 'Coordinates of Capital Cities';
   select Capital format=$20., Name 'Country' format=$20., 
          Latitude, Longitude
      from sql.countries a left join sql.worldcitycoords b
           on a.Capital = b.City and
              a.Name = b.Country
      order by Capital;

/*-------------------------------------------------------------------
   OUTPUT 3.16 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=10;
   title 'Populations of Capitals Only';
   select City format=$20., Country 'Country' format=$20., 
          Population
      from sql.countries right join sql.worldcitycoords 
           on Capital = City and
              Name = Country
      order by City;

/*-------------------------------------------------------------------
   OUTPUT 3.17 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=10;   
   title 'Populations and/or Coordinates of World Cities';
   select City '#City#(WORLDCITYCOORDS)' format=$20.,
          Capital '#Capital#(COUNTRIES)' format=$20.,
          Population, Latitude, Longitude
      from sql.countries full join sql.worldcitycoords
           on Capital = City and
              Name = Country; 

/*-------------------------------------------------------------------
   OUTPUT 3.18 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=10;   
   title 'Populations and/or Coordinates of World Cities';
   select coalesce(Capital, City,Name)format=$20. 'City',
          coalesce(Name, Country) format=$20. 'Country',
          Population, Latitude, Longitude
      from sql.countries full join sql.worldcitycoords
           on Capital = City and
           Name = Country; 

/*-------------------------------------------------------------------
   OUTPUT 3.19 

   Notes: The two DATA steps produce the datasets used by both
          the merge and the join. The merge and the join produce
          identical output.
-------------------------------------------------------------------*/
data fltsuper;
input Flight Supervisor $;
datalines;
145   Kang                 
150   Miller                
155   Evanko 
;

data fltdest;
input Flight Destination $;
datalines;
145   Brussels
150   Paris
155   Honolulu
;
run;

data merged;
   merge FltSuper FltDest;
   by Flight;
run;

proc print data=merged noobs;
   title 'Table MERGED';
run;

proc sql;
   title 'Table MERGED';
   select s.flight, Supervisor, Destination
      from fltsuper s, fltdest d
      where s.Flight=d.Flight;

/*-------------------------------------------------------------------
   OUTPUT 3.20 

   Notes: The two DATA steps produce the datasets used by both
          the merge and the join. The merge and the join produce
          identical output.
-------------------------------------------------------------------*/
data fltsuper;
input Flight Supervisor $;
datalines;
145   Kang                 
150   Miller                
155   Evanko 
157   Lei
;
data fltdest;
input Flight Destination $;
datalines;
145   Brussels
150   Paris
165   Seattle
;
run;

data merged;
   merge fltsuper fltdest;
   by flight;
run;
proc print data=merged noobs;
   title 'Table MERGED';
run;

proc sql;
   select coalesce(s.Flight,d.Flight) as Flight, Supervisor, Destination
      from fltsuper s full join fltdest d
           on s.Flight=d.Flight;

/*-------------------------------------------------------------------
   OUTPUT 3.21 

   Notes:
-------------------------------------------------------------------*/
data fltsuper;
input Flight Supervisor $;
datalines;
145   Kang                 
145   Rameriz               
150   Miller                
150   Picard                
155   Evanko                
157   Lei
;

data fltdest;
input Flight Destination $;
datalines;
145   Brussels
145   Edmonton
150   Paris
150   Madrid
165   Seattle
;
run;

data merged;
   merge fltsuper fltdest;
   by flight;
run;

proc print data=merged noobs;
   title 'Table MERGED';
run;

/*-------------------------------------------------------------------
   OUTPUT 3.22 

   Notes: This SQL code uses the same data sets as Output 3.21. The 
          data steps that create them are included here.
-------------------------------------------------------------------*/
data fltsuper;
input Flight Supervisor $;
datalines;
145   Kang                 
145   Rameriz               
150   Miller                
150   Picard                
155   Evanko                
157   Lei
;

data fltdest;
input Flight Destination $;
datalines;
145   Brussels
145   Edmonton
150   Paris
150   Madrid
165   Seattle
;
run;

proc sql;
   title 'Table JOINED';
   select *
      from fltsuper s, fltdest d
      where s.Flight=d.Flight;

/*-------------------------------------------------------------------
   OUTPUT 3.23 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'U.S. States with Population Greater than Belgium';
   select Name 'State' , population format=comma10. 
      from sql.unitedstates 
      where population gt
                  (select population from sql.countries
                      where name = "Belgium");

/*-------------------------------------------------------------------
   OUTPUT 3.24 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=5;
   title 'Populations of Major Oil Producing Countries';
   select name 'Country', Population format=comma15.
      from sql.countries
      where Name in 
            (select Country from sql.oilprod);

/*-------------------------------------------------------------------
   OUTPUT 3.25 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=5;  
   title 'Populations of NonMajor Oil Producing Countries';
   select name 'Country', Population format=comma15. 
      from sql.countries  
      where Name not in 
            (select Country from sql.oilprod);

/*-------------------------------------------------------------------
   OUTPUT 3.26 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'Oil Reserves of Countries in Africa';
   select * from sql.oilrsrvs o
      where 'Africa' =
           (select Continent from sql.countries c
               where c.Name = o.Country);

/*-------------------------------------------------------------------
   OUTPUT 3.27 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'Oil Reserves of Countries in Africa';
   select * from sql.oilrsrvs o
      where exists 
           (select Continent from sql.countries c
               where o.Country = c.Name and
                     Continent = 'Africa');

/*-------------------------------------------------------------------
   OUTPUT 3.28 

   Notes:
-------------------------------------------------------------------*/
proc sql;
  title 'Coordinates of African Cities with Major Oil Reserves';
  select * from sql.worldcitycoords 
     where country in
       (select Country from sql.oilrsrvs o        
           where o.Country in      
             (select Name from sql.countries c
                 where c.Continent = 'Africa'));

/*-------------------------------------------------------------------
   OUTPUT 3.29 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=10;
   title 'Neighboring Cities';
   select a.City format=$10., a.State,
          a.Latitude 'Lat', a.Longitude 'Long',
          b.City format=$10., b.State,
          b.Latitude 'Lat', b.Longitude 'Long',
          sqrt(((b.latitude-a.latitude)**2) +
               ((b.longitude-a.longitude)**2)) as dist format=6.1
      from sql.uscitycoords a, sql.uscitycoords b
      where a.city ne b.city and
            calculated dist =
            (select min(sqrt(((d.latitude-c.latitude)**2) +
                             ((d.longitude-c.longitude)**2)))
                from sql.uscitycoords c, sql.uscitycoords d
                where c.city = a.city and 
                      c.state = a.state and
                      d.city ne c.city)
      order by a.city;

/*-------------------------------------------------------------------
   OUTPUT 3.30 

   Notes:
-------------------------------------------------------------------*/
proc sql;
  title 'Table A';
  select * from sql.a;
  title 'Table B';
  select * from sql.b;

/*-------------------------------------------------------------------
   OUTPUT 3.31 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'A UNION B';
   select * from sql.a
   union
   select * from sql.b;

/*-------------------------------------------------------------------
   OUTPUT 3.32 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'A UNION ALL B';
   select * from sql.a
   union all
   select * from sql.b;

/*-------------------------------------------------------------------
   OUTPUT 3.33 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'A EXCEPT B';
   select * from sql.a
   except
   select * from sql.b;

/*-------------------------------------------------------------------
   OUTPUT 3.34 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'A EXCEPT ALL B';
   select * from sql.a
   except all
   select * from sql.b;

/*-------------------------------------------------------------------
   OUTPUT 3.35 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'A INTERSECT B';
   select * from sql.a
   intersect
   select * from sql.b;

/*-------------------------------------------------------------------
   OUTPUT 3.36 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'A OUTER UNION B';
   select * from sql.a
   outer union
   select * from sql.b;

/*-------------------------------------------------------------------
   OUTPUT 3.37 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'A OUTER UNION CORR B';
   select * from sql.a
   outer union corr
   select * from sql.b;

/*-------------------------------------------------------------------
   OUTPUT 3.38 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'A EXCLUSIVE UNION B';
   (select * from sql.a
   except
   select * from sql.b)
   union
   (select * from sql.b
   except
   select * from sql.a);

/*-------------------------------------------------------------------
   OUTPUT 4.1 

   Notes: 
-------------------------------------------------------------------*/
proc sql;
   create table sql.newstates
          (state char(2),           /* 2–character column for         */
                                    /* state abbreviation                   */

           date num                 /* column for date of entry into the US */
                informat=date9.     /* with an informat                     */  
                format=date9.,      /* and format of DATE9.                 */
                  
           population num);         /* column for population                */

proc sql;
   describe table sql.newstates;

/*-------------------------------------------------------------------
   OUTPUT 4.2 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=10;   
   title 'Densities of Countries';
   create table sql.densities as
      select Name 'Country' format $15.,
             Population format=comma10.0,
             Area as SquareMiles,
             Population/Area format=6.2 as Density
         from sql.countries;

   select * from sql.densities;

/*-------------------------------------------------------------------
   OUTPUT 4.3 

   Notes: 
-------------------------------------------------------------------*/
proc sql;
   describe table sql.densities;

/*-------------------------------------------------------------------
   OUTPUT 4.4 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   create table sql.newcountries
      like sql.countries;

   describe table sql.newcountries;

/*-------------------------------------------------------------------
   OUTPUT 4.5 

   Notes: This example uses the NEWCOUNTRIES table created in 
          Output 4.4. The table is recreated here.
          
          The output shown in the book is incorrect. This code, 
          when submitted, will insert two rows into 
          the table (with Name = "Bangladesh" and "Japan"). 
          Only these two rows should appear in the output. 
          Submitting the code below will generate the correct output.       
-------------------------------------------------------------------*/
proc sql;
   create table sql.newcountries
      like sql.countries;

proc sql;
   insert into sql.newcountries
      set name='Bangladesh',
          capital='Dhaka',
          population=126391060
      set name='Japan',
          capital='Tokyo',
          population=126352003;
      
   title "World's Largest Countries";
   select name format=$20., 
          capital format=$15.,
          population format=comma15.0
      from sql.newcountries;

/*-------------------------------------------------------------------
   OUTPUT 4.6 

   Notes: This example uses the NEWCOUNTRIES table created in 
          Output 4.4. The table is recreated here. 

          The output shown in the book is incorrect. This code, 
          when submitted, will insert two rows into 
          the table (with Name = "Pakistan" and "Nigeria"). 
          Only these two rows should appear in the output.  
          Submitting the code below will generate the correct output.         
-------------------------------------------------------------------*/
proc sql;
   create table sql.newcountries
      like sql.countries;

proc sql;
   insert into sql.newcountries
      values ('Pakistan', 'Islamabad', 123060000, ., ' ', .)
      values ('Nigeria', 'Lagos', 99062000, ., ' ', .); 
   title "World's Largest Countries";
   select name format=$20., 
          capital format=$15.,
          population format=comma15.0
      from sql.newcountries;

/*-------------------------------------------------------------------
   OUTPUT 4.7 

   Notes: This example uses the NEWCOUNTRIES table created in 
          Output 4.4. The table is recreated here.
-------------------------------------------------------------------*/

proc sql;
   create table sql.newcountries
      like sql.countries;

proc sql;
   title "World's Largest Countries";
   insert into sql.newcountries
   select * from sql.countries
      where population ge 130000000;

   select name format=$20., 
          capital format=$15.,
          population format=comma15.0
      from sql.newcountries;

/*-------------------------------------------------------------------
   OUTPUT 4.8 

   Notes: This example uses the NEWCOUNTRIES table as created 
          for Output 4.7. The table is recreated here.          
-------------------------------------------------------------------*/

/* Create and populate NEWCOUNTRIES */
proc sql;
   create table sql.newcountries like sql.countries;
   insert into sql.newcountries
   select * from sql.countries
      where population ge 130000000;
quit;
/*---------------------*/

proc sql;
   update sql.newcountries
      set population=population*1.05;
   title "Updated Population Values";
   select name format=$20., 
          capital format=$15.,
          population format=comma15.0
      from sql.newcountries;

/*-------------------------------------------------------------------
   OUTPUT 4.9

   Notes: This example uses the NEWCOUNTRIES table as created 
          for Output 4.7. The table is recreated here.
          The two SQL statements in this example
          produce the same output.
-------------------------------------------------------------------*/

/* Create and populate NEWCOUNTRIES */
proc sql;
   create table sql.newcountries like sql.countries;
   insert into sql.newcountries
   select * from sql.countries
      where population ge 130000000;
quit;
/*---------------------*/

proc sql;
   update sql.newcountries
      set population=population*1.05
         where name like 'B%';

   update sql.newcountries
      set population=population*1.07
         where name in ('China', 'Russia');

   title "Selectively Updated Population Values";
   select name format=$20., 
          capital format=$15.,
          population format=comma15.0
      from sql.newcountries;

update sql.newcountries
   set population=population*
      case when name like 'B%' then 1.05
           when name in ('China', 'Russia') then 1.07
           else 1
      end;

/*-------------------------------------------------------------------
   OUTPUT 4.10

   Notes: This example uses the NEWCOUNTRIES table as created 
          for Output 4.7. The table is recreated here.          
-------------------------------------------------------------------*/

/* Create and populate NEWCOUNTRIES */
proc sql;
   create table sql.newcountries like sql.countries;
   insert into sql.newcountries
   select * from sql.countries
      where population ge 130000000;
quit;
/*---------------------*/

proc sql;
   delete
      from sql.newcountries
      where name like 'R%';

/*-------------------------------------------------------------------

   OUTPUT 4.11 

   Notes: This example uses the NEWCOUNTRIES table as created 
          for Output 4.7. The table is recreated here.            
-------------------------------------------------------------------*/

/* Create and populate NEWCOUNTRIES */
proc sql;
   create table sql.newcountries like sql.countries;
   insert into sql.newcountries
   select * from sql.countries
      where population ge 130000000;
quit;
/*---------------------*/

proc sql;
   alter table sql.newcountries
      add density num label='Population Density' format=6.2;

   title "Population Density Table";
   select name format=$20., 
          capital format=$15.,
          population format=comma15.0,
          density
      from sql.newcountries;

/*-------------------------------------------------------------------
   OUTPUT 4.12 

   Notes: This example uses the NEWCOUNTRIES table as created 
          for Output 4.7. The table is recreated here.      
          The ALTER statement is included from Output 4.11.

          The two SQL statements in this example
          produce the same output.        
-------------------------------------------------------------------*/

/* Create and populate NEWCOUNTRIES */
proc sql;
   create table sql.newcountries like sql.countries;
   insert into sql.newcountries
   select * from sql.countries
      where population ge 130000000;
   alter table sql.newcountries
      add density num label='Population Density' format=6.2;
quit;
/*---------------------*/
proc sql;
   update sql.newcountries
      set density=population/area;

   title "Population Density Table";
   select name format=$20., 
          capital format=$15.,
          population format=comma15.0,
          density
      from sql.newcountries;

   create table sql.newcountries as
   select *, population/area as density
             label='Population Density' 
             format=6.2
      from sql.newcountries;

/*-------------------------------------------------------------------
   OUTPUT 4.13 

   Notes: This example uses the NEWCOUNTRIES table as created 
          for Output 4.7. The table is recreated here.               
-------------------------------------------------------------------*/

/* Create and populate NEWCOUNTRIES */
proc sql;
   create table sql.newcountries like sql.countries;
   insert into sql.newcountries
   select * from sql.countries
      where population ge 130000000;
quit;
/*---------------------*/

proc sql;
   title "World's Largest Countries";
   alter table sql.newcountries
      modify population format=comma15.;
   select name, population from sql.newcountries;

/*-------------------------------------------------------------------
   OUTPUT 4.14 

   Notes: This example uses the NEWCOUNTRIES table as created 
          for Output 4.7. The table is recreated here.              
-------------------------------------------------------------------*/

/* Create and populate NEWCOUNTRIES */
proc sql;
   create table sql.newcountries like sql.countries;
   insert into sql.newcountries
   select * from sql.countries
      where population ge 130000000;
quit;
/*---------------------*/
proc sql;
   title "World's Largest Countries";
   alter table sql.newcountries
      modify name char(60) format=$60.;
   update sql.newcountries
      set name='The United Nations member country is '||name;
  
   select name from sql.newcountries;

/*-------------------------------------------------------------------
   OUTPUT 4.15 

   Notes:
-------------------------------------------------------------------*/
proc means data=sql.countries mean maxdec=2;
   title "Mean Area for All Countries";
   var area;
run;

/*-------------------------------------------------------------------
   OUTPUT 4.16 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   create table sql.mystates
      (state      char(15), 
       population num,
       continent  char(15),

          /* contraint specifications */
       constraint prim_key    primary key(state),
       constraint population  check(population gt 0),
       constraint continent   check(continent in ('North America', 'Oceania')));      

   create table sql.uspostal
      (name      char(15),
       code      char(2) not null,          /* constraint specified as    */
                                            /* a column attribute         */
       
       constraint for_key foreign key(name) /* links NAME to the          */
                  references sql.mystates   /* primary key in MYSTATES    */

                     on delete restrict     /* forbids deletions to STATE */
                                            /* unless there is no         */
                                            /* matching NAME value        */

                     on update set null);   /* allows updates to STATE,   */
                                            /* changes matching NAME      */
                                            /* values to missing          */ 
       
proc sql;
   describe table sql.mystates;
   describe table constraints sql.uspostal;

/*-------------------------------------------------------------------
   OUTPUT 4.17 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'Current Population Information for Continents';
   create view sql.newcontinents as
   select continent,
          sum(population) as totpop  format=comma15. label='Total Population',
          sum(area) as totarea format=comma15. label='Total Area'
      from sql.countries
      group by continent;

   select * from sql.newcontinents;

/*-------------------------------------------------------------------
   OUTPUT 4.18 

   Notes: The view created for Output 4.17 is reproduced for this 
          DESCRIBE VIEW example.
-------------------------------------------------------------------*/
proc sql;
   title 'Current Population Information for Continents';
   create view sql.newcontinents as
   select continent,
          sum(population) as totpop  format=comma15. label='Total Population',
          sum(area) as totarea format=comma15. label='Total Area'
      from sql.countries
      group by continent;

proc sql;
   describe view sql.newcontinents;
/*-------------------------------------------------------------------
   OUTPUT 4.19 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'Countries With Population GT Caribbean Countries';
   select w.Name, w.Population format=comma15., c.TotCarib 
      from (select sum(population) as TotCarib format=comma15.
               from sql.countries
                  where continent = 'Central America and Caribbean') as c,
                    sql.countries as w
            where w.population gt c.TotCarib;

/*-------------------------------------------------------------------
   OUTPUT 5.1 

   Notes:
-------------------------------------------------------------------*/
proc sql feedback;
   select * from sql.countries;

/*-------------------------------------------------------------------
   OUTPUT 5.2 

   Notes:
-------------------------------------------------------------------*/
proc sql stimer ;
   select us.name, us.population
      from sql.unitedstates as us, sql.countries as w
      where us.population gt w.population and
            w.name = 'Belgium';
 
   select Name, population 
      from sql.unitedstates 
      where population gt
                  (select population from sql.countries
                      where name = 'Belgium');
/*-------------------------------------------------------------------
   OUTPUT 5.3 

   Notes:
-------------------------------------------------------------------*/
proc sql noprint;
   title 'Countries with Population Under 20,000';
   select Name, Population from sql.countries;
reset print number;
   select Name, Population from sql.countries 
      where population lt 20000;

/*-------------------------------------------------------------------
   OUTPUT 5.4 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   describe table dictionary.tables;

/*-------------------------------------------------------------------
   OUTPUT 5.5 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'All Tables and Views in the SQL Library';
   select libname, memname, memtype, nobs
      from dictionary.tables
      where libname='SQL';

/*-------------------------------------------------------------------
   OUTPUT 5.6 

   Notes:
-------------------------------------------------------------------*/
proc sql;
   title 'All Tables that Contain the Country Column';
   select libname, memname, name
      from dictionary.columns
      where name='Country' and
            libname='SQL';

/*-------------------------------------------------------------------
   OUTPUT 5.7 

   Notes:
-------------------------------------------------------------------*/
proc sql noprint;
   select country, barrels
      into :country1, :barrels1  
      from sql.oilrsrvs;
  
%put &country1 &barrels1;

/*-------------------------------------------------------------------
   OUTPUT 5.8 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=12;
   reset noprint;
   select max(AvgHigh)
      into :maxtemp
      from sql.worldtemps
      where country = 'Canada';
reset print;
   title "The Highest Temperature in Canada: &maxtemp";
   select city, AvgHigh format 4.1 
      from sql.worldtemps
      where country = 'Canada';

/*-------------------------------------------------------------------
   OUTPUT 5.9 

   Notes:
-------------------------------------------------------------------*/
proc sql noprint;
   select name, Population 
      into :country1 - :country4, :pop1 - :pop3
           from sql.countries;

%put &country1 &pop1;
%put &country2 &pop2;
%put &country3 &pop3;
%put &country4;

/*-------------------------------------------------------------------
   OUTPUT 5.10 

   Notes:
-------------------------------------------------------------------*/
proc sql noprint inobs=5;
   select Name
      into :countries separated by ', '
      from sql.countries;

%put &countries;

/*-------------------------------------------------------------------
   OUTPUT 5.11 

   Notes:
-------------------------------------------------------------------*/
proc sql noprint inobs=5;
   select Name
      into :countries separated by ',' NOTRIM
      from sql.countries;

%put &countries;

/*-------------------------------------------------------------------
   OUTPUT 5.12, 5.13

   Notes: The %ADDREF() macros at the end of this code should not
          be included in the PROC SQL step. See the text.           
-------------------------------------------------------------------*/
proc sql;
create table sql.referee
   (Name      char(15),
    Subject   char(15));

   /* define the macro */
%macro addref(name,subject);
%local count;

   /* are there three referees in the table? */
reset noprint;
   select count(*)
      into :count
      from sql.referee
      where subject="&subject";

%if &count ge 3 %then %do;
   reset print;
   title "ERROR: &name not inserted for subject -- subject..";
   title2 "        There are 3 referees already.";
   select * from sql.referee where subject="&subject";
   reset noprint;
   %end;

%else %do;
   insert into sql.referee(name,subject) values("&name","&subject");
   %put NOTE: &name has been added for subject -- subject..;
   %end;

%mend;

/*------------------------*/
%addref(Conner,sailing);
%addref(Fay,sailing);
%addref(Einstein,relativity);
%addref(Smythe,sailing);
%addref(Naish,sailing);

/*-------------------------------------------------------------------
   OUTPUT 5.14 

   Notes:
-------------------------------------------------------------------*/
proc sql noprint;
   select * from sql.countries;

%put SQLOBS=*&sqlobs* SQLOOPS=*&sqloops* SQLRC=*&sqlrc*;

/*-------------------------------------------------------------------
   OUTPUT 5.15 

   Notes:
-------------------------------------------------------------------*/
proc sql outobs=10;
   title 'US Cities';
   select State, City, Latitude, Longitude
      from sql.uscitycoords
      order by state;

/*-------------------------------------------------------------------
   OUTPUT 5.16 

   Notes: The GROUP BY in the PROC SQL step shown in the book
          should be ORDER BY. It is correct in the code shown here.
-------------------------------------------------------------------*/
proc sql noprint;
   create table sql.cityreport as
   select * 
      from sql.uscitycoords
      order by state;

proc report data=sql.cityreport 
            headline 
            headskip;
   title 'Coordinates of U.S. Cities in Pacific Rim States';
   column state city ('Coordinates' latitude longitude);
   define state / order format=$2. width=5 'State';
   define city / order format=$15. width=15 'City';
   define latitude / display format=4. width=8 'Latitude';
   define longitude / display format=4. width=9 'Longitude';
   where state='AK' or 
         state='HI' or 
         state='WA' or 
         state='OR' or 
         state='CA';
run;

/*-------------------------------------------------------------------
   OUTPUT 5.17 

   Notes: This example assumes a PAYROLL table exists in an 
          ORACLE database.
-------------------------------------------------------------------*/
libname mydblib oracle user=user-id
        password=password
        path=path-name schema=schema-name;

proc sql;
   select jobcode label='Jobcode',
          sum(salary) as total 
          label='Total for Group' 
          format=dollar11.2
      from mydblib.payroll
      group by jobcode;
quit;
/*-------------------------------------------------------------------
   OUTPUT 5.18 

   Notes: This example assumes a SCHEDULE table exists in an 
          ORACLE database.
-------------------------------------------------------------------*/
libname mydblib oracle user=user-id
 password=password
proc sql; 
   create view LON as 
   select flight, dates, idnum
      from mydblib.schedule 
      where dest='LON';
quit;

proc print data=work.LON noobs;
run;

/*-------------------------------------------------------------------
   OUTPUT 5.19 

   Notes: This example assumes a STAFF table exists in an 
          ORACLE database.
-------------------------------------------------------------------*/
proc sql outobs=15;
   connect to oracle as ora2 (user=user-id password=password);
   select * from connection to ora2 (select lname, fname, state from staff);
   disconnect from ora2;
quit;

/*-------------------------------------------------------------------
   OUTPUT 5.20 

   Notes: See the text for information concerning filenames.
-------------------------------------------------------------------*/
ods html body='odsout.htm'; 
   proc sql outobs=12;
      title 'U.S. Cities with Their States and Coordinates';
      select * 
         from sql.uscitycoords;
ods html close;

/*-------------------------------------------------------------------
   OUTPUT 6.1 

   Notes: The DATA step creates the data set used in the example.
-------------------------------------------------------------------*/
data Sample;
   do i=1 to 10;
      Value=2983*ranuni(135);
      Weight=33*rannor(579);
      if mod(i,2)=0 then Gender='M';
         else Gender='F';
      output;
   end;
   drop i;


proc print data=Sample;
  title 'Sample Data for Weighted Average';
run;

/*-------------------------------------------------------------------
   OUTPUT 6.2 

   Notes:
-------------------------------------------------------------------*/
data Sample;
   do i=1 to 10;
      Value=2983*ranuni(135);
      Weight=33*rannor(579);
      if mod(i,2)=0 then Gender='M';
         else Gender='F';
      output;
   end;
   drop i;

proc sql;
   title 'Weighted Averages from Sample Data';
   select Gender, sum(Value*Weight)/sum(Weight) as WeightedAverage
      from (select Gender, Value,
                   case
                      when Weight gt 0 then Weight
                      else 0
                   end as Weight
               from Sample)
      group by Gender;

/*-------------------------------------------------------------------
   OUTPUT 6.3 

   Notes: Creates and displays data sets
-------------------------------------------------------------------*/
data oldstaff;
 input id $ Last : $10. First $ Middle $ Phone $ Location $;
 datalines;
5463 Olsen Mary K. 661-0012 R2342
6574 Hogan Terence H. 661-3243 R4456
7896 Bridges Georgina W. 661-8897 S2988
4352 Anson Sanford . 661-4432 S3412
5674 Leach Archie G. 661-4328 S3533
7902 Wilson Fran R. 661-8332 R4454
0001 Singleton Adam O. 661-0980 R4457
9786 Thompson Jack . 661-6781 R2343
;
data newstaff;
 input id $ Last : $10. First $ Middle $ Phone $ Location $;
 datalines;
5463 Olsen Mary K. 661-0012 R2342
6574 Hogan Terence H. 661-3243 R4456
7896 Bridges Georgina W. 661-2231 S2987
4352 Anson Sanford . 661-4432 S3412
5674 Leach Archie G. 661-4328 S3533
7902 Wilson Fran R. 661-8332 R4454
0001 Singleton Adam O. 661-0980 R4457
9786 Thompson John C. 661-6781 R2343
2123 Chen Bill W. 661-8099 R4432


proc sql;
 title 'Old Staff Table';
 select * from oldstaff;
 title 'New Staff Table';
 select * from newstaff;

/*-------------------------------------------------------------------
   OUTPUT 6.4 

   Notes: Uses the data set from the previous example. The DATA step 
          that creates it is included here.
-------------------------------------------------------------------*/
data oldstaff;
 input id $ Last : $10. First $ Middle $ Phone $ Location $;
 datalines;
5463 Olsen Mary K. 661-0012 R2342
6574 Hogan Terence H. 661-3243 R4456
7896 Bridges Georgina W. 661-8897 S2988
4352 Anson Sanford . 661-4432 S3412
5674 Leach Archie G. 661-4328 S3533
7902 Wilson Fran R. 661-8332 R4454
0001 Singleton Adam O. 661-0980 R4457
9786 Thompson Jack . 661-6781 R2343
;
data newstaff;
 input id $ Last : $10. First $ Middle $ Phone $ Location $;
 datalines;
5463 Olsen Mary K. 661-0012 R2342
6574 Hogan Terence H. 661-3243 R4456
7896 Bridges Georgina W. 661-2231 S2987
4352 Anson Sanford . 661-4432 S3412
5674 Leach Archie G. 661-4328 S3533
7902 Wilson Fran R. 661-8332 R4454
0001 Singleton Adam O. 661-0980 R4457
9786 Thompson John C. 661-6781 R2343
2123 Chen Bill W. 661-8099 R4432

proc sql;
   title 'Updated Rows';
   select * from newstaff 
   except
   select * from oldstaff;

/*-------------------------------------------------------------------
   OUTPUT 6.5 

   Notes:
-------------------------------------------------------------------*/
data league1;
input @1 Fullname $20. @21 Bowler $4. @29 AvgScore 3.;
cards;
Alexander Delarge   4224    164
John T Chance       4425
Jack T Colton       4264
                    1412    141
Andrew Shepherd     4189    185
;

data league2;
input @1 FirstName $10. @12 LastName $15. @28 AMFNo $4. @38 AvgScore 3.;
cards;
Alex       Delarge         4224      156
Mickey     Raymond         1412
                           4264      174
Jack       Chance          4425
Patrick    O'Malley        4118      164
;

proc sql;
title 'Bowling Averages from League1';
select * from league1;
title 'Bowling Averages from League2';
select * from league2;

/*-------------------------------------------------------------------
   OUTPUT 6.6 

   Notes: Uses the data set from the previous example. The DATA step 
          that creates it is included here.
-------------------------------------------------------------------*/
data league1;
input @1 Fullname $20. @21 Bowler $4. @29 AvgScore 3.;
cards;
Alexander Delarge   4224    164
John T Chance       4425
Jack T Colton       4264
                    1412    141
Andrew Shepherd     4189    185
;

data league2;
input @1 FirstName $10. @12 LastName $15. @28 AMFNo $4. @38 AvgScore 3.;
cards;
Alex       Delarge         4224      156
Mickey     Raymond         1412
                           4264      174
Jack       Chance          4425
Patrick    O'Malley        4118      164
;

proc sql;
   title "Averages from Last Year's League When Possible";
   title2 "Supplemented when Available from Prior Year's League";
   select coalesce(lastyr.fullname,trim(prioryr.firstname)
                   ||' '||prioryr.lastname)as Name format=$26.,
          coalesce(lastyr.bowler,prioryr.amfno)as Bowler,
          coalesce(lastyr.avgscore,prioryr.avgscore)as Average format=8.
      from league1 as lastyr full join league2 as prioryr
           on lastyr.bowler=prioryr.amfno
      order by Bowler;

/*-------------------------------------------------------------------
   OUTPUT 6.7 

   Notes:
-------------------------------------------------------------------*/
data survey;
    input State $ Answer $ @@;
    datalines;
NY YES NY YES NY YES NY YES NY YES NY YES NY NO  NY NO  NY NO  NC YES 
NC YES NC YES NC YES NC YES NC YES NC YES NC YES NC YES NC YES NC YES
NC YES NC YES NC YES NC YES NC YES NC YES NC YES NC YES NC YES NC NO
NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO
NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO
NC NO  NC NO  NC NO  PA YES PA YES PA YES PA YES PA YES PA YES PA YES
PA YES PA YES PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  
PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  PA NO
VA YES VA YES VA YES VA YES VA YES VA YES VA YES VA YES VA YES VA YES
VA YES VA YES VA YES VA YES VA YES VA YES VA YES VA YES VA YES VA NO
VA NO  VA NO  VA NO  VA NO  VA NO  VA NO  VA NO  VA NO  VA NO  VA NO
VA NO  VA NO  VA NO  VA NO  VA NO  VA NO


proc print data=Survey(obs=10);
  title 'Sample Data for Subtotal Percentages';
run;

/*-------------------------------------------------------------------
   OUTPUT 6.8 

   Notes: Uses the data set from the previous example. The DATA step 
          that creates it is included here.
-------------------------------------------------------------------*/
data survey;
    input State $ Answer $ @@;
    datalines;
NY YES NY YES NY YES NY YES NY YES NY YES NY NO  NY NO  NY NO  NC YES 
NC YES NC YES NC YES NC YES NC YES NC YES NC YES NC YES NC YES NC YES
NC YES NC YES NC YES NC YES NC YES NC YES NC YES NC YES NC YES NC NO
NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO
NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO  NC NO
NC NO  NC NO  NC NO  PA YES PA YES PA YES PA YES PA YES PA YES PA YES
PA YES PA YES PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  
PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  PA NO  PA NO
VA YES VA YES VA YES VA YES VA YES VA YES VA YES VA YES VA YES VA YES
VA YES VA YES VA YES VA YES VA YES VA YES VA YES VA YES VA YES VA NO
VA NO  VA NO  VA NO  VA NO  VA NO  VA NO  VA NO  VA NO  VA NO  VA NO
VA NO  VA NO  VA NO  VA NO  VA NO  VA NO

proc sql;
   title1 'Survey Responses';
   select survey.Answer, State, count(State) as Count,
          calculated Count/Subtotal as Percent format=percent8.2
   from survey,
        (select Answer, count(*) as Subtotal from survey
            group by Answer) as survey2
   where survey.Answer=survey2.Answer
   group by survey.Answer, State;
quit;

/*-------------------------------------------------------------------
   OUTPUT 6.9 

   Notes:
-------------------------------------------------------------------*/
data Duplicates;
   input LastName $ FirstName $ City $ State $;
   datalines;
Smith John Richmond Virginia
Johnson Mary Miami Florida
Smith John Richmond Virginia
Reed Sam Portland Oregon
Davis Karen Chicago Illinois
Davis Karen Chicago Illinois
Thompson Jennifer Houston Texas
Smith John Richmond Virginia
Johnson Mary Miami Florida
;
   

proc print data=Duplicates;
  title 'Sample Data for Counting Duplicates';
run;
/*-------------------------------------------------------------------
   OUTPUT 6.10 

   Notes: Uses the data set from the previous example. The DATA step 
          that creates it is included here.
-------------------------------------------------------------------*/
data Duplicates;
   input LastName $ FirstName $ City $ State $;
   datalines;
Smith John Richmond Virginia
Johnson Mary Miami Florida
Smith John Richmond Virginia
Reed Sam Portland Oregon
Davis Karen Chicago Illinois
Davis Karen Chicago Illinois
Thompson Jennifer Houston Texas
Smith John Richmond Virginia
Johnson Mary Miami Florida
;

proc sql;
   title 'Duplicate Rows in DUPLICATES Table';
   select *, count(*) as Count
      from Duplicates
      group by LastName, FirstName, City, State
      having count(*) > 1;
     
/*-------------------------------------------------------------------
   OUTPUT 6.11 

   Notes:
-------------------------------------------------------------------*/

data Employees;
   input ID $ LastName $ FirstName $ Supervisor $;
   datalines;
1001 Smith John 1002
1002 Johnson Mary None
1003 Reed Sam None
1004 Davis Karen 1003
1005 Thompson Jennifer 1002
1006 Peterson George 1002
1007 Jones Sue 1003
1008 Murphy Janice 1003
1009 Garcia Joe 1002
;

proc print data=Employees;
  title 'Sample Data for Expanding a Hierarchy';
run;

/*-------------------------------------------------------------------
   OUTPUT 6.12 

   Notes: Uses the data set from the previous example. The DATA step 
          that creates it is included here.
-------------------------------------------------------------------*/
data Employees;
   input ID $ LastName $ FirstName $ Supervisor $;
   datalines;
1001 Smith John 1002
1002 Johnson Mary None
1003 Reed Sam None
1004 Davis Karen 1003
1005 Thompson Jennifer 1002
1006 Peterson George 1002
1007 Jones Sue 1003
1008 Murphy Janice 1003
1009 Garcia Joe 1002
;

proc sql;
   title 'Expanded Employee and Supervisor Data';
   select A.ID label="Employee ID",
          trim(A.FirstName)||' '||A.LastName label="Employee Name",
          B.ID label="Supervisor ID",
          trim(B.FirstName)||' '||B.LastName label="Supervisor Name"
      from Employees A, Employees B
      where A.Supervisor=B.ID and A.Supervisor is not missing;

/*-------------------------------------------------------------------
   OUTPUT 6.13 

   Notes:
-------------------------------------------------------------------*/
data Sales;
   input Salesperson $ January February March;
   datalines;
Smith 1000 650 800
Johnson 0 900 900
Reed 1200 700 850
Davis 1050 900 1000
Thompson 750 850 1000
Peterson 900 600 500
Jones 800 900 1200
Murphy 700 800 700
Garcia 400 1200 1150
;
   

proc print data=Sales;
  title 'Sample Data for Summarizing Data from Multiple Columns';
run;

/*-------------------------------------------------------------------
   OUTPUT 6.14 

   Notes: Uses the data set from the previous example. The DATA step 
          that creates it is included here.
-------------------------------------------------------------------*/
data Sales;
   input Salesperson $ January February March;
   datalines;
Smith 1000 650 800
Johnson 0 900 900
Reed 1200 700 850
Davis 1050 900 1000
Thompson 750 850 1000
Peterson 900 600 500
Jones 800 900 1200
Murphy 700 800 700
Garcia 400 1200 1150
;

proc sql;
   title 'Total First Quarter Sales';
   select sum(January)  as JanTotal,
          sum(February) as FebTotal,
          sum(March)    as MarTotal,
          sum(calculated JanTotal, calculated FebTotal,
              calculated MarTotal) as GrandTotal format=dollar10.
      from Sales;

/*-------------------------------------------------------------------
   OUTPUT 6.15 

   Notes:
-------------------------------------------------------------------*/
data sales;
  input Site $ Product $ Invoice $ InvoiceAmount InvoiceDate $;
  datalines;
V1009  VID010  V7679 598.5  980126
V1019  VID010  V7688 598.5  980126
V1032  VID005  V7771  1070  980309
V1043  VID014  V7780  1070  980309
V421   VID003  V7831  2000  980330
V421   VID010  V7832   750  980330
V570   VID003  V7762  2000  980302
V659   VID003  V7730  1000  980223
V783   VID003  V7815   750  980323
V985   VID003  V7733  2500  980223
V966   VID001  V5020  1167  980215
V98    VID003  V7750  2000  980223
;

proc sql;
  title 'Sample Data to Create Summary Sales Report';
  select * from sales;
quit;

/*-------------------------------------------------------------------
   OUTPUT 6.16 

   Notes: Uses the data set from the previous example. The DATA step 
          that creates it is included here.
-------------------------------------------------------------------*/
data sales;
  input Site $ Product $ Invoice $ InvoiceAmount InvoiceDate $;
  datalines;
V1009  VID010  V7679 598.5  980126
V1019  VID010  V7688 598.5  980126
V1032  VID005  V7771  1070  980309
V1043  VID014  V7780  1070  980309
V421   VID003  V7831  2000  980330
V421   VID010  V7832   750  980330
V570   VID003  V7762  2000  980302
V659   VID003  V7730  1000  980223
V783   VID003  V7815   750  980323
V985   VID003  V7733  2500  980223
V966   VID001  V5020  1167  980215
V98    VID003  V7750  2000  980223
;

proc sql;
   title 'First Quarter Sales by Product';
   select Product, 
          sum(Jan) label='Jan', 
          sum(Feb) label='Feb',
          sum(Mar) label='Mar'
      from (select Product,
                   case 
                      when substr(InvoiceDate,3,2)='01' then
                         InvoiceAmount end as Jan,
                   case
                      when substr(InvoiceDate,3,2)='02' then 
                         InvoiceAmount end as Feb,
                   case
                      when substr(InvoiceDate,3,2)='03' then
                         InvoiceAmount end as Mar
               from work.sales)
      group by Product;

/*-------------------------------------------------------------------
   OUTPUT 6.17 

   Notes:
-------------------------------------------------------------------*/
data chores;
   input Project $ Hours Season $;
   datalines;
weeding 48 summer
pruning 12 winter
mowing 36 summer
mulching 17 fall
raking 24 fall
raking 16 spring
planting 8 spring
planting 8 fall
sweeping 3 winter
edging 16 summer
seeding 6 spring
tilling 12 spring
aerating 6 spring
feeding 7 summer
rolling 4 winter
;

proc sql;
title 'Garden Chores';
select * from chores;
quit;

/*-------------------------------------------------------------------
   OUTPUT 6.18 
 
   Notes: Uses the data set from the previous example. The DATA step 
          that creates it is included here.
-------------------------------------------------------------------*/
data chores;
   input Project $ Hours Season $;
   datalines;
weeding 48 summer
pruning 12 winter
mowing 36 summer
mulching 17 fall
raking 24 fall
raking 16 spring
planting 8 spring
planting 8 fall
sweeping 3 winter
edging 16 summer
seeding 6 spring
tilling 12 spring
aerating 6 spring
feeding 7 summer
rolling 4 winter
;
proc sql;
    title 'Garden Chores by Season in Logical Order';
    select Project, Hours, Season
       from (select Project, Hours, Season,
               case
                  when Season = 'spring' then 1
                  when Season = 'summer' then 2
                  when Season = 'fall' then 3
                  when Season = 'winter' then 4
                  else .
               end as Sorter
               from chores)
       order by Sorter;

/*-------------------------------------------------------------------
   OUTPUT 6.19 

   Notes:
-------------------------------------------------------------------*/
data incentives;
   input @1 Name $18. @20 Department $2. Payrate 
            Gadgets Whatnots;
   datalines;
Lao Che            M2   8.00  10193  1105
Jack Colton        U2   6.00   9994  2710
Mickey Raymond     M1  12.00   6103  1930
Dean Proffit       M2  11.00   3000  1999
Antoinette Lily    E1  20.00   2203  4610
Sydney Wade        E2  15.00   4205  3010
Alan Traherne      U2   4.00   5020  3000
Elizabeth Bennett  E1  16.00  17003  3003
;

proc sql;
  title 'Sales Data for Incentives Program';
  select * from incentives;
quit;

/*-------------------------------------------------------------------
   OUTPUT 6.20 

   Notes: Uses the data set from the previous example. The DATA step 
          that creates it is included here.
-------------------------------------------------------------------*/
data incentives;
   input @1 Name $18. @20 Department $2. Payrate 
            Gadgets Whatnots;
   datalines;
Lao Che            M2   8.00  10193  1105
Jack Colton        U2   6.00   9994  2710
Mickey Raymond     M1  12.00   6103  1930
Dean Proffit       M2  11.00   3000  1999
Antoinette Lily    E1  20.00   2203  4610
Sydney Wade        E2  15.00   4205  3010
Alan Traherne      U2   4.00   5020  3000
Elizabeth Bennett  E1  16.00  17003  3003
;

proc sql;
   update incentives
   set payrate = case
                    when gadgets > 10000 then
                       payrate + 5.00
                    when gadgets > 5000 then
                       case
                          when department in ('E1', 'E2') then
                             payrate + 2.00
                          else payrate + 3.00
                       end
                    else payrate
                 end;
   update incentives
   set payrate = case
                    when whatnots > 2000 then
                       case
                          when department in ('E2', 'M2', 'U2') then
                             payrate + 1.00
                          else payrate + 0.50
                       end
                    else payrate
                 end;
   title 'Adjusted Payrates Based on Sales of Gadgets and Whatnots';
   select * from incentives;