1: Three ways to build a table
Building a table will only create a table in the library and display it in the log, and there will be no output.
1.1: Define columns yourself to create an empty table
Column-specification = column-define + column-constriants + message=/msgtype
Data in SAS is stored in only two ways, one char (n), and one Num. SAS also supports SQL native data types, but will eventually be translated into both
procSQL;Create Table Work. Departments (Deptvarchar( -) label='Department', CodeintegerLabel='Dept Code', the Managervarchar( -), auditdate num format=date9.); Quit;
1.2: Create an empty table that is identical to the other table
drop/keep= option to keep or delete the specified columns, they can be placed behind any table
proc SQL; Create Table Work . Flightdelays3 (drop=delaycategory destinationtype) like sasuser.flightdelays (keep=delaycategory destinationtype delay); Table Work . Flightdelays3;quit;
1.3: Create a table based on the query results
For newly generated columns in Select if the alias is not used, the SAS will automatically start a name
proc SQL; create table work . ticketagents as select LastName, FirstName, Jobcode, salary from Sasuser.payrollmaster, sasuser.staffmaster where payrollmaster.empid = Span style= "color: #000000;" > Staffmaster.empid and jobcode contains " ta
2: Presentation forms, and restrictions in tables
TABLE Table -name-1<table-name-n>;
The full build statement is generated in the log!
Proc Contents Data=libref.tablename can also
/* Limit conditions in the show Table */
TABLE Table -name-1<table-name-n>;
3: Insert row data, here are two common ways
All columns are inserted at the end of the table
proc SQL; Insert into Work . newtable (Item,qty) /* specifies the column to insert, without writing the default is all columns */ Values ('FLUTE',6) Values ('VIOLIN',3); quit;
4: Delete Row
DELETE from Table - < WHERE expression>;
Without where, the contents of the entire table will be deleted
5: Create a table with a constraint on the column (cannot be applied to the view)
Two different ways
Creating a Constraint in a Column specification
procSQL;Create Table Work. Employees
/ * Follow the restrictions directly after the variable * / (IDChar(5)Primary Key, NameChar(Ten), GenderChar(1) not NULL Check(Genderinch('M','F'), hdate date label='Hire Date');
Creating a Constraint by Using a Constraint specification
procSQL;Create Table Work. Discount3 (DestinationChar(3), begindate num Format=date9.,enddate num Format=Date9.,discount num,
/ * Write the constraint as a separate line * /constraintOk_discountCheck(Discount le.5),constraintNotnull_dest notNULL(destination)); quit;
6: Handling Input errors
Using the undo_policy= Option to Control UNDO processing
If the specified constraint is not met when the row is inserted, the undo policy is
For example, in undo_policy= by default, two rows are inserted, the first row is satisfied, the second row is not satisfied, and neither row is inserted.
In the case of Undo_policy=none, the insertion succeeds and the failure is not inserted.
7: Update form, two ways to use case when
Partial updates and full table updates
procSQL;Update Work. Payrollmaster_new2SetSalary=Salary* CaseSUBSTR (Jobcode,3,1) when '1' Then 1.05 when '2' Then 1.10 when '3' Then 1.15 Else 1.08End; quit;
8: Change the columns in the table
To add, drop (delete), or modify columns in a table, use the ALTER table St Atement.
/* add column */
proc SQL; alter table work .payrollmaster4 add Bonus num format= comma10. 2 , level char (3
/* Delete Column */
Proc SQL; Alter Table Work . Payrollmaster4 Drop Bonus,level; quit;
can use the MODIFY clause to change a column ' s
1:length (column width)-For a character column only
2:informat
3:format
4:label.
You cannot change the type of a column, such as Char->num
Cannot change column name
/*modify column */
proc SQL; alter table work .payrollmaster4modify salary format = dollar11. 2 label= "Salaryamt"; quit;
/* Three actions can be used together */
Proc SQL; Alter Table Work . Payrollmaster4 Add Age nummodify dateofhire date format = Mmddyy10. Drop dateOfBirth, gender;
9: Delete Table
DROP TABLE Table -name-1<table-name-n>;
SQL in SAS (6) Creating tables, presenting tables, inserting rows, deleting rows, restricting conditions (constriants), handling input errors (undo policies), update tables, changing columns