CH4 COMBINE TABLE VERTICALLY
1.process a set operation: first process two select clause separately and then join together by the operator.
process multiple operators: process first two, use the result join the next, and the next.
INTERSECT will be evaluated first, then the rest.
2. no matter how many tables join together by set operator, only one semicolon needed(at last.)
3. when there are duplicate rows, use(INTERSECT, EXCEPT, UNION). PROC SQL eliminate the duplicate rows first and then find rows that meet the criteria.
4. 有名字按名字来操作，没有名字按位置来操作。新的列名字是第一个select clause的。如果第一个没有名字再用第二个的。
5. 必须亮同样的data type 才可以一起联合。否则有warning
6.
用ALL keyword，不删除重复行。不能和outter UNION一起用。
7. 用 CORR, 如果和 INTERSECT, EXCEPT ,UNION一起用，合并match列，删除不match的列。
如果和OUTTER UNION一起，除了合并相应列，还会把没有match的列显示。 如果有alias，按照alias match，不是实际列名。
8. 在第一个pass， sql remove the duplicated rows. the second pass, proc sql run by operator.
9. if use EXCEPT ALL, sql do not remove duplicated rows, only pass once by operator.
10. if use EXCEPT CORR, sql only looks lat the same-name column, remove all un-matched columns. and run by operators by the only matched column.
11. if except all corr, used all together. process ALL first by not remove duplicated rows. and then run CORR, by remove unmatched column and then run by operators.
12. use keyword ALL will speed up the process time, since it prevent SAS process data again.
13. INTERSECT works the same way as EXCEPT. remove duplicate row, and then run...
14. INTERSECT plus ALL , CORR works the same way too.
15. for UNION, PROC SQL concatenate two tables first and then remove the duplicate rows.
16. 用 UNION ALL, 就是两个表的简单堆砌。
17. OUTER UNION 显示所有的行和列，不会合并列。
18. OUTER UNION CORR 会合并同名的列，其他的照显示不误。
19. OUTER UNION CORR 和 DATA NEW_DATA; SET D1 D2; RUN; 一样。
QUIZ:
1. c
2. d
3. d
4. a
5. c------------------------------------a
6. d------------------------------------b
7. b
8. c
9. b-----------------------------------c
10. a
CH5 Creating and Managing Tables Using PROC SQL
1. Notice the differences between:
CREATE TABLE xxx (xxx char(x)....)
CREATE TABLE xxx LIKE xxx
CREATE TABLE xxx AS SELECT .......
2. When creating new table, the columns should be separated by commas, and the data type and column names are separated by space.
3. By default, width of a column is 8 digits.
3.5 PROC SQL; DESCRIBE TABLE xxxxxx; to see table information without seeing data.
4. To drop some columns from the original tables to create new table, example like:
CREATE TABLE xxxxx (DROP = Column1 Column2) LIKE xxxxxx
5. When copying a table, such as
PROC SQL; CREATE TABLE xxxxx AS SELECT * FROM xxxxxx;
the order from the new table may be different from the original one, unless using order by.
6. INserting rows by set clause.
INSERT INTO xxxx SET col1=value1, col2=value2, ....
if adding another row, use another SET clause. Not require order, since column name specified.
7. inserting by value clause
INSERT INTO xxxxx (optional column name)
values ()
values ()
if insert all values in order for all column, do not need to add column names.
if for some columns, need to add optional column names.
specify missing values ' ' for character variable and . for numeric.
8. INSERTING by a query result.
PROC SQL; INSERT INTO xxxxxx SELECT .... FROM... WHERE....
9. INTEGRITY CONSTRAINT ( I personally think it is very important.)
define integrity constraint example:
CREATE TABLE xxxxxx
(ID char(5) primary key,
Gender char(1) not null check(gender in ('M', 'F')));
10. Create a constraint in constraint specification
example:
must give a name for constraint.
11. when inserting values into a table with constraint, if there are one values not comply with the constraint, SAS will deleting all the inserted values. (by default, can be changed)
if there are more than one values that not complying the constraint, SAS log only shows the first one.
12. UNDO_POLICY = REQUIRE NONE OPTIONAL to specify what to do the undo process.
example: PROC SQL UNDO_POLICY = NONE; ......
13. only to see a table's constraint.
proc sql; describe table constraints tablename;
14. updating all rows:
proc sql; update tablename set expression(or equation) where....;
15. when updating subset of rows. there are two ways.
(1) using multiple update and set clause
(2) using a single update clause and contain CASE expression.
CASE WHEN THEN ELSE END; if there is no ELSE clause, it will be missing.
CASE expression can also be used in INSERT and SELECT statement.
16. when there is a case expression, proc sql first find the value in case expression to fill the equation, and then evaluate the equation for each row.
17 updating rows do not create any new rows.
18. in case expression, if the function used multiple time, it can be expressed one time in case operand. Example:
it can be used only hten the function is a '=' comparison.
19. use CASE expression in SELECT statement. example:
20. PROC SQL; DELETE from tablename where expression;
to delete all rows , just remove where expression.
21. ALTER TABLE deals with columns.
22. proc sql; alter table tablename ADD newcolumnname type format, newcolumn2 type;
23 PROC SQL; ALTER TABLE tablename DROP column1, column2;
24 use alter table cannot change character column to numeric, or the column name. it can change column length, informat, format and label.
25. PROC SQL; ALTER TABLE tablename MODIFY columnname format=... label=...;
26. delete a entire table. PROC SQL; DROP TABLE tablename;
27 The CREATE TABLE statement generates only a table as output, not a report
QUIZ
1. b
2. a
3. c
4. b
5. d
6. b
7. a
8. d
9. c
10. a
