SQL in SAS (6) Creating tables, presenting tables, inserting rows, deleting rows, restricting conditions (constriants), handling input errors (undo policies), update tables, changing columns

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