*Slide 4;
data ce1;
input ssn :$11. code degree :$9. Grade;
datalines;
123-00-5526 135 Bachelors 9
206-00-1454 135 Bachelors 10
323-00-2746 126 Masters 9
367-00-9580 126 Bachelors 9
564-00-6508 126 Bachelors 9
782-00-8284 135 Masters 10
354-00-9603 126 Masters 10
590-00-6170 135 Bachelors 8
060-00-2046 126 Bachelors 9
983-00-6455 135 Masters 9
638-00-0350 135 Bachelors 9
861-00-4706 126 Bachelors 9

;
run;



*Slide 5;
data hw1;
input ssn :$11. code degree :$9. Grade;
datalines;
367-00-9580 126 Bachelors 19
564-00-6508 126 Bachelors 19
060-00-2046 126 Bachelors 17
123-00-5526 135 Bachelors 20
206-00-1454 135 Bachelors 19
638-00-0350 135 Bachelors 19
861-00-4706 126 Bachelors 20
983-00-6455 135 Masters 19
782-00-8284 135 Masters 19
354-00-9603 126 Masters 19
590-00-6170 135 Bachelors 18
;
run;


*Slide 7;
proc sql;
select ce1.code label="Major Code",ce1.degree label="Degree",ce1.grade as ce1 label="Computer Exercise 1", 
hw1.grade as hw1 label="Homework 1" 
from ce1 left join hw1 on ce1.ssn=hw1.ssn;
quit;

*Slide 8;
proc sql;
select code, avg(ce1) as ce1avg label="CE 1" format=4.1, avg(hw1) as hw1avg label="HW 1" format=5.1 from 
(select ce1.code label="Major Code",ce1.degree label="Degree",ce1.grade as ce1 label="Computer Exercise 1", 
hw1.grade as hw1 label="Homework 1" 
from ce1 left join hw1 on ce1.ssn=hw1.ssn)
group by code;
quit;

*Slide 9;
proc sql;
create view Major_Comp as  select code, avg(ce1) as ce1avg label="CE 1" format=4.1, avg(hw1) as hw1avg label="HW 1" format=5.1 from 
(select ce1.code label="Major Code",ce1.degree label="Degree",ce1.grade as ce1 label="Computer Exercise 1", 
hw1.grade as hw1 label="Homework 1" 
from ce1 left join hw1 on ce1.ssn=hw1.ssn)
group by code;
select * from Major_Comp;
quit;

*Slide 10;
proc sql;
create view JoinGrades as select ce1.code label="Major Code",ce1.degree label="Degree",ce1.grade as ce1 label="Computer Exercise 1", 
hw1.grade as hw1 label="Homework 1" 
from ce1 left join hw1 on ce1.ssn=hw1.ssn
order by code;
quit;

*Slide 11;
proc means data=JoinGrades maxdec=2; 
by code;
var ce1 hw1;
run;

*Slide 12;
proc sql;
describe view Major_Comp JoinGrades;
quit;

*Slide 16;

proc sql;
create view work.joingradesstat as 
select * from work.joingrades
where code eq 135
using libname work; 
quit;

*Slide 18;
proc sql;
drop view work.joingradesstat;
quit;


* Slide 17;

* The following does not work;
proc sql;
 update JoinGrades 
  set hw1 = hw1+5
  where code=135;
 quit;

* Creating a view that does not include the confidential SSN column;
proc sql; 
 create view hw1v as 
 select code label="Major Code", 
 degree label="Degree", grade as hw1 label="Homework 1", 100*(grade/20) as pctgrade label="Homework 1 percentage" 
 from hw1;
quit;

/* Does not work */
proc sql;
 update hw1v 
  set pctgrade = pctgrade+5
  where code=135;
 quit;
 
* Works, and filters through to original hw1 table;

proc sql;
 update hw1v 
  set hw1 = hw1+5
  where code=135;
 quit;