曾经是那么的意气风发，年级前三名，众人眼中的娇子。曾经那么的志向远大，考取人大，觉得以后一定会指点江山。
渐渐的，那个被寄予厚望的自己越跑越快，把我这个真实的本体越拉越远。不曾体会到这种差距，是因为思绪依旧存留在那个快步奔跑着的自己。而本相则远在后方碘着大肚子踱着方步。
渐渐的，以前的光环消逝了。来到了平庸的学校，在和一群平庸的学生比较中，那个快跑的我似乎跑的更加快了，而那个真实的我似乎更满足于那个快跑着的速度。完全忽略自己好久好久都只是站在原地在欣赏着前方的幻象。
许久之后，学校快不能呆了。慢慢的我要找一个公司。去面试的时候，发现每个人的两个自我都是手挽手肩并肩的前来。只有我一个人孤单的去。呼喊前面那个自己，早已没了踪影。
Travelers的拒绝，让我久违的思绪瞬时间回到了我的本身。他告诉我，其实那个快跑的我看着很远，其实更远。而要追上他，要把一些东西从我肥硕的身体上切下来，也要把一些在我旁边始终如一跟随我的东西吃进去，放到心里。
我拿着刀，在自己充满脂肪的身体上游荡，久久不愿动第一刀。他们太诱人了，他们跟随了我好久好久，从小就和我一起，我已经习惯了在他们里面的舒服与安逸。我大喊着：别人身上也有，为什么我一定要切掉？！没有声音回答我。
算了，不切了。还是先吃吧。摆在我面前的两样东西，我很早就应该吃下去放到心里。却始终逃避。 对琳琳的爱，和对神的爱。 我要把这两个吃下去，放到心里。
为了我最爱的琳琳，我什么苦都愿意吃。她是我的爱人，和我一起欢乐，和我一起悲伤，和我一起经历所有的事情，不离不弃。她是我一生的伴侣。为了她我愿意。
刚刚才对上帝发怨言，抱怨，说了好多难听的话。但我知道他依旧爱着我，过了激动的时段，平静下来后，我知道他没有离开我，只是我觉得他没和我一起。我知道我刚才流的眼泪他都装到了袋子里。他也在和我一起忧伤。即使在痛苦，他也会帮我们度过。
再见吧，懒惰， 为了琳琳和上帝。鲜血直流，我要咬着牙把你切掉。
再见吧，安逸、懦弱、逃避、不专心、不求甚解、没有目标、没有计划、浪费时间、一些没有意义的小事情。。。 鲜血染红了我的身体，我丝毫不后悔。
虽然流着血，我却身体轻盈了好多。伤口？那不是伤口，而是荣耀。神会用时间和大能抚平他们。
有了琳琳和神， 嘿！我，我要来了！
我跑啊跑，去追上那个曾经被自己寄予厚望的自己。
2013年9月30日星期一
2013年9月19日星期四
Notes for SAS Advanced Programmer_CH10
CH10
1. Remember that when both macro language statements and SAS language statements occur in the same step, the macro processor executes macro language statements before any SAS language statements are executed.
Because the %LET statements are always processed by the macro processor
before the DATA step is executed, the value of foot will always be whatever the last
%LET statement assigns.
2. When you use the SYMPUT routine to create a macro variable in a DATA step, the
macro variable is not actually created and assigned a value until the DATA step is
executed. Therefore, you cannot successfully reference a macro variable that is created
with the SYMPUT routine by preceding its name with an ampersand within the same
DATA step in which it is created.
3. To use a literal with the SYMPUT routine, you enclose the literal string in quotation
marks.
CALL SYMPUT(‘macro-variable’, ‘text’);
4. example： call symput(’foot’,’Some Fees Are Unpaid’);
5.
6. The SYMPUTX routine is very similar to the SYMPUT routine. In addition to
creating a macro variable and assigning a value to it, the SYMPUTX routine also
automatically removes leading and trailing blanks from both arguments.
7. CALL SYMPUTX(macro-variable,expression);
e.g.: call symputx(’crsname’,course_title);
call symputx(’date’,put(begin_date,mmddyy10.));
call symputx(’due’,put(fee*(total-paidup),dollar8.));
8. 另一种方法来用call symput(macrovariableName, expression)
e.g.: call symput(’numpaid’,trim(left(paidup)));
call symput(’numstu’,trim(left(total)));
call symput(’crsname’,trim(course_title));
9. You can use the PUT function to
perform explicit numeric-to-character conversions
format the result of a numeric expression.
10.
11. In this example, you use one call to the SYMPUT routine in order to create one
macro variable for each value of the DATA step variable Course_code and to assign the
corresponding value of Course_title to each macro variable. That is, for each
observation in Sasuser.Courses, the macro processor will create a new macro variable.
The new macro variable will have the same name as the value of the data set variable
Course_code for that observation. The value of the new macro variable will be the
value of the data set variable Course_title for that observation.
data _null_;
set sasuser.courses;
call symput(course_code, trim(course_title));
run;
%put _user_; 用一个 call symput 建立了好多个macro variable 在data step里。
12.
1. Remember that when both macro language statements and SAS language statements occur in the same step, the macro processor executes macro language statements before any SAS language statements are executed.
Because the %LET statements are always processed by the macro processor
before the DATA step is executed, the value of foot will always be whatever the last
%LET statement assigns.
2. When you use the SYMPUT routine to create a macro variable in a DATA step, the
macro variable is not actually created and assigned a value until the DATA step is
executed. Therefore, you cannot successfully reference a macro variable that is created
with the SYMPUT routine by preceding its name with an ampersand within the same
DATA step in which it is created.
3. To use a literal with the SYMPUT routine, you enclose the literal string in quotation
marks.
CALL SYMPUT(‘macro-variable’, ‘text’);
4. example： call symput(’foot’,’Some Fees Are Unpaid’);
5.
values of numeric variables are automatically converted to character values, using
the BEST12. format.
6. The SYMPUTX routine is very similar to the SYMPUT routine. In addition to
creating a macro variable and assigning a value to it, the SYMPUTX routine also
automatically removes leading and trailing blanks from both arguments.
7. CALL SYMPUTX(macro-variable,expression);
e.g.: call symputx(’crsname’,course_title);
call symputx(’date’,put(begin_date,mmddyy10.));
call symputx(’due’,put(fee*(total-paidup),dollar8.));
8. 另一种方法来用call symput(macrovariableName, expression)
e.g.: call symput(’numpaid’,trim(left(paidup)));
call symput(’numstu’,trim(left(total)));
call symput(’crsname’,trim(course_title));
9. You can use the PUT function to
perform explicit numeric-to-character conversions
format the result of a numeric expression.
10.
11. In this example, you use one call to the SYMPUT routine in order to create one
macro variable for each value of the DATA step variable Course_code and to assign the
corresponding value of Course_title to each macro variable. That is, for each
observation in Sasuser.Courses, the macro processor will create a new macro variable.
The new macro variable will have the same name as the value of the data set variable
Course_code for that observation. The value of the new macro variable will be the
value of the data set variable Course_title for that observation.
data _null_;
set sasuser.courses;
call symput(course_code, trim(course_title));
run;
%put _user_; 用一个 call symput 建立了好多个macro variable 在data step里。
12.
13. options symbolgen;
data _null_;
set sasuser.schedule;
call symput(’teach’||left(course_number),
trim(teacher));
run;
14.
this happens at Data step execution time.
15.
This form of the INTO clause does not trim leading or trailing blanks. Also, the
INTO clause cannot be used when you create a table or a view.
%let totalfee=&totalfee;
Note: This form of the INTO clause does not trim leading or trailing blanks, but the
%LET statement removes any leading or trailing blanks that are stored in the value of
totalfee.
16. create multiple macro variables in PROC SQL;
proc sql;
select course_code, location, begin_date format=mmddyy10.
into :crsid1-:crsid3,
:place1-:place3,
:date1-:date3
from sasuser.schedule
where year(begin_date)=2002
order by begin_date;
quit;
17. 当不知道需要建立多少个macro variable 的时候，先建立一个macro variable是一共多少行，然后和别的需要的连接在一起，例子如下：
18.
这个是只想建立一个 macro variable但是要存储很多个值的情况。
19. proc sql noprint;
select distinct location into :sites separated by ’ ’
from sasuser.schedule;
quit;
20. 用macro view 在 sql view里。
21.
QUIZ:
1. d----------c macro functions are handled before any SAS statement, not macro variable.
2. a in symput, the macro variable name and value name, both need ' '
3. d
4. c-----b If you enclose the DATA step
variable name in quotation marks in the SYMPUT routine, the new macro
variable will have the same name as the DATA step variable rather than having
the DATA step variable’s value as a name.
5. b
6. d
7. c
8. c
9. b----d price=symget("daily_fee"); needs " "
10. c in proc sql, needs input() to convert character to numeric.
Notes for SAS Advanced Programmer_CH8-Ch9
CH8
1. Remember that PROC SQL options are specified in the PROC SQL statement
2. After you specify an option, it remains in effect until you change it or you re-invoke
PROC SQL.
3.
4.proc sql inobs = 5; means only read 5 rows from each table specified in the query.
5. proc sql number; list a column in the output, which is the row number. number and nonumber is like obs | noobs in print precedure.
6. proc sql double; or nodouble; means double space, do not affect HTML output.
7. flow | noflow | flow=n | flow= n m 其实就是让每一列的内容自动换行，这样的话就不会让最后几列窜到前几列去。 对list output 有用， 对HTML pdf rtf 没用。n和m 是行宽度的范围。
8. STIMER | NOSTIMER option is showing the time for each SELECT statement. in order to use stimer | nostimer in proc sql. we have to make sure the stimer option in SAS system option is in effect by checking
proc options option = stimer value; run;
stimer | nostimer show time after the QUIT; statement.
9 RESET optionname; just add or reset other options. not affect other pre-set options.
10. Dictionary tables. created each time they are referenced in a SAS program; updated automatically; limited to read-only access.
11. proc sql;
select memname
from dictionary.columns
where libname=’SASUSER’ 先使用 describe table dictionaryname 去看这个dictionary里面有什么，然后用这个query来选择和加条件。
12. 我感觉dictionary table 就是把某一个library下面各个数据的属性的一个集合，放到一个特殊的文件里。
13. LOOPS = option 限制的proc sql 的inner loop 的次数。 可以用 PROMPT | NOPROMPT来 选择达到最大loop次数的时候，继续不继续。
14. EXEC | NOEXEC 先是check 命令的准确性，如果用exec，没错误了就继续执行命令。
15. 在exec in effect 时， 用 ERRORSTOP | NOERRORSTOP 去让SAS 遇到错误停不停。
16.After you specify an option, it remains in effect until you change it or you
re-invoke PROC SQL.
The DOUBLE | NODOUBLE and the FLOW | NOFLOW | FLOW=n| FLOW=n
m options do not affect the appearance of HTML, PDF, or RTF output that is
created with the Output Delivery System.
If you query a Dictionary table about the files in a specific library, the library
name used in the WHERE clause must be specified in uppercase letters because
that is how it is stored in SAS. Column names used in the WHERE clause must be
specified in the same case as they appear in the Dictionary table.
QUIZ:
1. a
2. b
3. c
4. d
5. d
6. d
7. d
8.d
9. b
10. b-----a
CH9Introducing Macro Variables
1. There are two types of macro variables:
automatic macro variables, which are provided by SAS
user-defined macro variables, whose values you create and define.
2. If you need to reference a macro variable within quotation
marks, such as in a title, you must use double quotation marks. The macro processor
will not resolve macro variable references that appear within single quotation marks.
3. When you use the %LET statement to define macro variables, you should keep in
mind the following rules:
All values are stored as character strings.
Mathematical expressions are not evaluated.
The case of the value is preserved.
Quotation marks that enclose literals are stored as part of the value.
Leading and trailing blanks are removed from the value before the assignment is
made.
4. A SAS program can be any combination of
DATA steps and PROC steps
global statements
SAS Component Language (SCL) code
Structured Query Language (SQL) code
SAS macro language code.
5.
General form, OPTIONS statement with SYMBOLGEN option:
OPTIONS NOSYMBOLGEN | SYMBOLGEN;
where
NOSYMBOLGEN
specifies that log messages about macro variable references will not be displayed. This is the
default.
SYMBOLGEN
specifies that log messages about macro variable references will be displayed.
6. 用 %put 后面加各种文字或者Macro variables， 先是macro variable的值。 也可以用%put _all_ 显示所有macro variable。或者%put _automatic_ 或者 _user_
7. 档Macro variable里面有特殊的符号，你不想让SAS 误会的话，就用 %STR
eg: %let prog=%str(data new; x=1; run;);
%let prog=data new%str(;) x=1%str(;)run%str(;);
如果macro variable 里面有单引号的话，要在前面加%， 例如：
%let text=%str(Joan%’s Report);
%let text=Joan%str(%’)s Report; 这两个的结果都是 Joan's Report
如果要把&和%也当做constant的话， 就要用 %nrstr(). eg: %let Period=%nrstr(May&Jun);
8.
12.
13.
14.The LEFT function expects only one argument, but you are passing “June 7, 2007” to
it. It interprets the comma as the delimiter between two arguments.
You can mask the comma by using the %QSYSFUNC function instead, as follows:
title "Report Produced on
%sysfunc(left(%qsysfunc(today(),worddate.)))";
The modified statement generates the following title:
Report Produced on June 7, 2007
15. 如果macrovariable 和别的字母连起来了让SAS分辨不出来了，就用一个点(.) 来分离。那个店在编译结束后不会出现。
16.
17. If you reference a macro variable within quotation marks, you must use double
quotation marks. Macro variable references that are enclosed in single quotation
marks will not be resolved.
QUIZ:
1. b
2. c
3. a
4. d
5. d 如果macro variable里有太多的空格，SAS只留一个空格。
6. d
7. c
8. d --------b
9. c
10. c
1. Remember that PROC SQL options are specified in the PROC SQL statement
2. After you specify an option, it remains in effect until you change it or you re-invoke
PROC SQL.
3.
4.proc sql inobs = 5; means only read 5 rows from each table specified in the query.
5. proc sql number; list a column in the output, which is the row number. number and nonumber is like obs | noobs in print precedure.
6. proc sql double; or nodouble; means double space, do not affect HTML output.
7. flow | noflow | flow=n | flow= n m 其实就是让每一列的内容自动换行，这样的话就不会让最后几列窜到前几列去。 对list output 有用， 对HTML pdf rtf 没用。n和m 是行宽度的范围。
8. STIMER | NOSTIMER option is showing the time for each SELECT statement. in order to use stimer | nostimer in proc sql. we have to make sure the stimer option in SAS system option is in effect by checking
proc options option = stimer value; run;
stimer | nostimer show time after the QUIT; statement.
9 RESET optionname; just add or reset other options. not affect other pre-set options.
10. Dictionary tables. created each time they are referenced in a SAS program; updated automatically; limited to read-only access.
11. proc sql;
select memname
from dictionary.columns
where libname=’SASUSER’ 先使用 describe table dictionaryname 去看这个dictionary里面有什么，然后用这个query来选择和加条件。
12. 我感觉dictionary table 就是把某一个library下面各个数据的属性的一个集合，放到一个特殊的文件里。
13. LOOPS = option 限制的proc sql 的inner loop 的次数。 可以用 PROMPT | NOPROMPT来 选择达到最大loop次数的时候，继续不继续。
14. EXEC | NOEXEC 先是check 命令的准确性，如果用exec，没错误了就继续执行命令。
15. 在exec in effect 时， 用 ERRORSTOP | NOERRORSTOP 去让SAS 遇到错误停不停。
16.After you specify an option, it remains in effect until you change it or you
re-invoke PROC SQL.
The DOUBLE | NODOUBLE and the FLOW | NOFLOW | FLOW=n| FLOW=n
m options do not affect the appearance of HTML, PDF, or RTF output that is
created with the Output Delivery System.
If you query a Dictionary table about the files in a specific library, the library
name used in the WHERE clause must be specified in uppercase letters because
that is how it is stored in SAS. Column names used in the WHERE clause must be
specified in the same case as they appear in the Dictionary table.
QUIZ:
1. a
2. b
3. c
4. d
5. d
6. d
7. d
8.d
9. b
10. b-----a
CH9Introducing Macro Variables
1. There are two types of macro variables:
automatic macro variables, which are provided by SAS
user-defined macro variables, whose values you create and define.
2. If you need to reference a macro variable within quotation
marks, such as in a title, you must use double quotation marks. The macro processor
will not resolve macro variable references that appear within single quotation marks.
3. When you use the %LET statement to define macro variables, you should keep in
mind the following rules:
All values are stored as character strings.
Mathematical expressions are not evaluated.
The case of the value is preserved.
Quotation marks that enclose literals are stored as part of the value.
Leading and trailing blanks are removed from the value before the assignment is
made.
4. A SAS program can be any combination of
DATA steps and PROC steps
global statements
SAS Component Language (SCL) code
Structured Query Language (SQL) code
SAS macro language code.
5.
General form, OPTIONS statement with SYMBOLGEN option:
OPTIONS NOSYMBOLGEN | SYMBOLGEN;
where
NOSYMBOLGEN
specifies that log messages about macro variable references will not be displayed. This is the
default.
SYMBOLGEN
specifies that log messages about macro variable references will be displayed.
6. 用 %put 后面加各种文字或者Macro variables， 先是macro variable的值。 也可以用%put _all_ 显示所有macro variable。或者%put _automatic_ 或者 _user_
7. 档Macro variable里面有特殊的符号，你不想让SAS 误会的话，就用 %STR
eg: %let prog=%str(data new; x=1; run;);
%let prog=data new%str(;) x=1%str(;)run%str(;);
如果macro variable 里面有单引号的话，要在前面加%， 例如：
%let text=%str(Joan%’s Report);
%let text=Joan%str(%’)s Report; 这两个的结果都是 Joan's Report
如果要把&和%也当做constant的话， 就要用 %nrstr(). eg: %let Period=%nrstr(May&Jun);
8.
%let text=%bquote(Joan’s Report); 直接就行。也有%NRBQUOTE;
9.
%qupcase()直接对于特殊符号不作变动。
10.
%qsubstr 不会编译解释特殊符号
11. The %INDEX function
searches source for the first occurrence of string
returns a number representing the position in source of the first character of string
when there is an exact pattern match
returns 0 when there is no pattern match.
e.g.: %index(&a, v) look for the first position of v in &a.
12.
%qscan() 不会编译解释特殊符号。
13.
14.The LEFT function expects only one argument, but you are passing “June 7, 2007” to
it. It interprets the comma as the delimiter between two arguments.
You can mask the comma by using the %QSYSFUNC function instead, as follows:
title "Report Produced on
%sysfunc(left(%qsysfunc(today(),worddate.)))";
The modified statement generates the following title:
Report Produced on June 7, 2007
15. 如果macrovariable 和别的字母连起来了让SAS分辨不出来了，就用一个点(.) 来分离。那个店在编译结束后不会出现。
16.
17. If you reference a macro variable within quotation marks, you must use double
quotation marks. Macro variable references that are enclosed in single quotation
marks will not be resolved.
QUIZ:
1. b
2. c
3. a
4. d
5. d 如果macro variable里有太多的空格，SAS只留一个空格。
6. d
7. c
8. d --------b
9. c
10. c
Notes for SAS Advanced Programmer_Ch4-CH5
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:
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.
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
订阅：
博文 (Atom)