Proc SQL

Question 1

The following datastep executed to create the dataset suntimes containing sunrise and sunset times.

data suntimes;
merge sunrise(in=a) sunset(in=b)
by date ;
run;

How would you write this in SQL?

Answer 1

This would be a full join in SQL terms.
We would take all records from set A and B and observations inside the intersection of C.

Under the assumption there are no missing observations in the key variable (in this case date) we can write this step as follows:

proc sql;
   create table suntimes as
      select * 
      from sunrise a
      full join sunset b 
      on a.date=b.date ;
quit ;

If the key variable does however contain missing values, the coalesce function should be used and the step should be written in the following manner.

proc sql;
   create table suntimes as
      select coalesce(a.date, b.date) as date, risetime, settime
      from sunrise a
      full join sunset b 
      on a.date=b.date ;
quit ;

Question 2

Give an example of proc sql step with a having statement that contains a non-corolated subquery.

Answer 2

Model answer
proc sql;    
   select jobcode, avg(salary) as AvgSalary format=dollar11.2  
   from sasuser.payrollmaster       
   group by jobcode      
   having avg(salary) >
       (select avg(salary) 
        from sasuser.payrollmaster);
quit ;

Question 3

If there are multiple records in a subquery, what operator can you use

Answer 3

The in operator

Question 4

What type of join is the following statement

proc sql ;
   select * 
   from a, b 
   where a.x = b.x ;
quit ;

Answer 4

An Inner Join

Question 5

Describe what a full outer join does

Answer 5

Matching and non-macthing rows in dataset a and dataset b. i.e. a&b a b.

Question 6

How does a left outer join work

Answer 6

Returns all data from dataset a and matching rows of datatset b i.e. a&b a.

Question 7

How does a right outer join work

Answer 7

Returns all data from dataset b and matching rows of datatset a i.e. a&b b.

Question 8

data merged ;
merge a b ;
by x;
run ;

What type of merge is this and what rows will it contain, given that all values in dataset a have a matching value of x for dataset b,

Answer 8

An inner join, this will contain all data in the intersect of a and b

Question 9

data merged ;
merge a b ;
by x;
run ;

What type of merge is this and what rows will it contain, given that NOT all values in dataset a have a matching value of x for dataset b,

Answer 9

An full outer join and will contain all rows non-matching rows in dataset a, dataset b and matching rows.

Question 10

When should you use the coalesce during an SQL join

Answer 10

When you want an outer join to produce the same results as a data-step match-merge

Question 11

Write the equivalent proc sql statement for this match merge.

data ten ;
merge seven eight ;
by nine ;
run ;

Answer 11

proc sql ;
   create table ten as
   select coalesce(a.nine,b.nine) as nine, var1,  var2
   from seven a
   full join eight b 
   on a.nine=b.nine ;
quit ;

Question 12

Why might you use proc sql joins

Give three reasons

Answer 12

Tables do not need to be indexed or sorted
names can be different for columns
Other comparison operators can be used and not just the equal sign

Question 13

What is an in-line view?

Answer 13

A nested query specified in the outer query’s from clause. It exists only during the execution of the step.

e.g.
from (select bread_type, filling
from sasuser.sandwiches)

Question 14

Will the columns be overlaid in this SQL statment if there are more fields in filling than sandwich?

proc sql  ;
   select * from sandwich a
   inner join filling b
   on a.id=b.id ;
quit ;

Answer 14

No because both sandwich and filling contain a separate ID fields. Also only fields in the intersection will be reported only.

Question 15

What does the corr or corresponding function do on intersect outer

Answer 15

Removes columns not present in both tables.