Syntax, CREATE VIEW
statement:
CREATE VIEW proc-sql-view AS
SELECT column-1<,
... column-n>
FROM table-1 | view-1<,
... table-n | view-n>
<optional
query clauses>;
|
proc sql;
create view certadv.faview as
select lastname, firstname ,
int((today()-dateofbirth)/365.25) as Age,
substr(jobcode,3,1) as Level,
salary
from certadv.payrollmaster,
certadv.staffmaster
where jobcode contains 'FA' and
staffmaster.empid=
payrollmaster.empid;
quit;
NOTE: SQL view CERTADV.FAVIEW has been defined.
proc sql; select * from certadv.faview; quit;
Syntax, DESCRIBE VIEW
statement:
DESCRIBE VIEW proc-sql-view-1<,...proc-sql-view-n>;
proc-sql-view
specifies a PROC SQL
view and can be one of the following:
|
proc sql;
describe view certadv.faview;
quit;
NOTE: SQL view CERTADV.FAVIEW is defined as: select lastname, firstname, INT((TODAY() - dateofbirth) / 365.25) as Age, SUBSTR(jobcode, 3, 1) as Level, salary from CERTADV.PAYROLLMASTER, CERTADV.STAFFMASTER where jobcode contains 'FA' and (staffmaster.empid = payrollmaster.empid);
proc sql;
create view certadv.payrollv as
select *
from certadv.payrollmaster;
quit;
proc sql;
create view certadv.payrollv as
select *
from payrollmaster;
quit;
Syntax, USING clause:
USING libname-clause-1<,...
libname-clause-n>;
libname-clause
is one of the following:
|
proc sql;
create view certadv.payrollv as
select*
from airline.payrollmaster
using libname airline 'SAS-library-one';
quit;
proc print data=certadv.payrollv;
run;
proc sql; create view certadv.infoview as select * from fa1.info outer union corr select * from fa2.info outer union corr select * from fa3.info; quit;
proc sql; create view certadv.raisev as select empid, jobcode, salary format=dollar12., salary/12 as MonthlySalary format=dollar12. from certadv.payrollmaster; quit; proc sql; select * from certadv.raisev where jobcode in ('PT2','PT3'); quit;
PT3
.
Though MonthlySalary is a derived column and cannot be changed using
an UPDATE statement, it will be updated because it is derived from
Salary.proc sql;
update certadv.raisev
set salary=salary * 1.20
where jobcode='PT3';
quit;
NOTE: 2 rows were updated in CERTADV.RAISEV.
PT3
.proc sql; select * from certadv.raisev where jobcode in ('PT2', 'PT3'); quit;
Syntax, DROP VIEW statement:
DROP VIEW view-name-1 <,...view-name-n>;
view-name
specifies a SAS data
view of any type (PROC SQL view or DATA step view) and can be one
of the following:
|
proc sql;
drop view certadv.raisev;
quit;
NOTE: View CERTADV.RAISEV has been dropped.