[下载]Lecture Notes: SAS Proc SQL

<FONT color=#0000cc size=-2><B>[PDF]</B></FONT> <a href="http://www2.sas.com/proceedings/sugi26/p060-26.pdf" target="_blank" ><FONT color=#551a8b>SUGI 26: </FONT><FONT color=#cc0033>PROC</FONT><FONT color=#551a8b> </FONT><FONT color=#cc0033>SQL</FONT><FONT color=#551a8b> -- Is It a Required Tool of Good </FONT><FONT color=#cc0033>SAS</FONT><FONT color=#551a8b>(r) Programming?</FONT></A>

<P><FONT style="FONT-WEIGHT: 700; FONT-SIZE: 9pt" face=Verdana color=#ff00ff>Section: SAS SQL:</FONT></P><FONT style="FONT-SIZE: 9pt"><FONT face=Verdana><P>Top 10 SAS SQL examples:</P><P><a href="http://www2.sas.com/proceedings/sugi29/042-29.pdf" target="_blank" >http://www2.sas.com/proceedings/sugi29/042-29.pdf</A> </P><P>A great simple SAS SQL teaching document</P><P><a href="http://www.pauldickman.com/teaching/sas/proc_sql_code_20041026.pdf" target="_blank" >http://www.pauldickman.com/teaching/sas/proc_sql_code_20041026.pdf</A> </P><P>A nice article about INTO Clause</P><P><a href="http://www.ats.ucla.edu/stat/sas/library/nesug98/p193.pdf" target="_blank" >http://www.ats.ucla.edu/stat/sas/library/nesug98/p193.pdf</A> </P><P>Undocumented and Hard to find SQL Features</P><P><a href="http://www2.sas.com/proceedings/sugi28/019-28.pdf" target="_blank" >http://www2.sas.com/proceedings/sugi28/019-28.pdf</A> </P><P>Part of this new book - Proc SQL: Beyond the Basics Using SAS - By Kirk Paul Lafler where a great collection of codes (To make sense you may benefit well buying the book - look at SAS site for the book)</P><P><a href="http://www.sas.com/samples/A58316" target="_blank" >http://www.sas.com/samples/A58316</A> </P></FONT></FONT>

<b><FONT size=3>Getting Started With SQL Procedure: Version 6
</FONT></b>by <a href="http://www.amazon.com/exec/obidos/search-handle-url/index=books&amp;field-author=Sas/002-9489533-6476818" target="_blank" ><FONT color=#003399>Sas</FONT></A>
Filled with code-based examples, this book is ideal for SAS programmers who want to take advantage of the power of the SQL procedure. A part of base SAS, PROC SQL is a powerful reporting tool that can often be an alternative to the DATA step and other SAS procedures. PROC SQL also provides a method for accessing data in external databases for those users who have a SAS/ACCESS license. This book shows examples of how you can use PROC SQL to create, update, modify, and combine SAS data sets, generate reports and more. The last chapter introduces the SQL Query Window in SAS/ASSIST software, which provides a point-and-click interface to PROC SQL. The SQL Query Window chapter contains an example of accessing data in another vendor's database management system. <STYLE type=text/css><!--ul.linkBullets      { padding-left: 0; }ul.linkBullets li   { list-style-type: none; margin-top: 1em; margin-bottom: 1em; }ul.noteBullets      { padding-left: 0; }ul.noteBullets li   { list-style-type: none; }span.darkRedWarning { color: #990000; }--></STYLE>

<H1><FONT size=5><A>The SQL Procedure</A></FONT></H1><img src="http://www.caspur.it/risorse/softappl/doc/sas_docs/common/images/contrul.gif">
<TABLE><TR><TD colSpan=3><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/z19view.htm" target="_blank" ><B>Overview</B></A> <TD></TD><TR><TD colSpan=3><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/z10ntax.htm" target="_blank" ><B>Procedure Syntax</B></A> <TD></TD><TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/z11stmt.htm" target="_blank" >PROC SQL Statement</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zertable.htm" target="_blank" >ALTER TABLE Statement</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zconnect.htm" target="_blank" >CONNECT Statement</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zeateidx.htm" target="_blank" >CREATE INDEX Statement</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/ztetable.htm" target="_blank" >CREATE TABLE Statement</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zateview.htm" target="_blank" >CREATE VIEW Statement</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zldelete.htm" target="_blank" >DELETE Statement</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zescribe.htm" target="_blank" >DESCRIBE Statement</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/z1nect.htm" target="_blank" >DISCONNECT Statement</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zsqldrop.htm" target="_blank" >DROP Statement</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zexecute.htm" target="_blank" >EXECUTE Statement</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zlinsert.htm" target="_blank" >INSERT Statement</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zqlreset.htm" target="_blank" >RESET Statement</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zlselect.htm" target="_blank" >SELECT Statement</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zlupdate.htm" target="_blank" >UPDATE Statement</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zalidate.htm" target="_blank" >VALIDATE Statement</A> <TR><TD colSpan=3><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/z0063531.htm" target="_blank" ><B>Component Dictionary</B></A> <TD></TD><TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zsqlbtwn.htm" target="_blank" >BETWEEN condition</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zsqlcalc.htm" target="_blank" >CALCULATED</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zsqlcase.htm" target="_blank" >CASE expression</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zlcoldef.htm" target="_blank" >column-definition</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zlcolmod.htm" target="_blank" >column-modifier</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zcolname.htm" target="_blank" >column-name</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zlconnto.htm" target="_blank" >CONNECTION TO</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zontains.htm" target="_blank" >CONTAINS condition</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zsqldict.htm" target="_blank" >DICTIONARY tables</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zlexists.htm" target="_blank" >EXISTS condition</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zlincond.htm" target="_blank" >IN condition</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zliscond.htm" target="_blank" >IS condition</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zljoined.htm" target="_blank" >joined-table</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zikecond.htm" target="_blank" >LIKE condition</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zueryexp.htm" target="_blank" >query-expression</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zlsqlexp.htm" target="_blank" >sql-expression</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zsumfunc.htm" target="_blank" >summary-function</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zableexp.htm" target="_blank" >table-expression</A> <TR><TD colSpan=3><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/z0146905.htm" target="_blank" ><B>Concepts</B></A> <TD></TD><TR><TD colSpan=3><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zsqlansi.htm" target="_blank" ><B>PROC SQL and the ANSI Standard</B></A> <TD></TD><TR><TD colSpan=3><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/z0146906.htm" target="_blank" ><B>Examples</B></A> <TD></TD><TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/z2sqlex1.htm" target="_blank" >Example 1: Creating a Table and Inserting Data into It</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/z2sqlex2.htm" target="_blank" >Example 2: Creating a Table from a Query's Result</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/z2sqlex3.htm" target="_blank" >Example 3: Updating Data in a PROC SQL Table</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/z2sqlex4.htm" target="_blank" >Example 4: Joining Two Tables</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/z2sqlex5.htm" target="_blank" >Example 5: Combining Two Tables</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/z2sqlex7.htm" target="_blank" >Example 6: Reporting from DICTIONARY Tables</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/z2sqlex8.htm" target="_blank" >Example 7: Performing an Outer Join</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/z2sqlex9.htm" target="_blank" >Example 8: Creating a View from a Query's Result</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zsqlex10.htm" target="_blank" >Example 9: Joining Three Tables</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zsqlex11.htm" target="_blank" >Example 10: Querying an In-Line View</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zsqlex12.htm" target="_blank" >Example 11: Retrieving Values with the SOUNDS-LIKE Operator</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zsqlex14.htm" target="_blank" >Example 12: Joining Two Tables and Calculating a New Value</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zsqlex15.htm" target="_blank" >Example 13: Producing All the Possible Combinations of the Values in a Column</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zsqlex16.htm" target="_blank" >Example 14: Matching Case Rows and Control Rows</A> <TR><TD></TD><TD></TD><TD><a href="http://www.caspur.it/risorse/softappl/doc/sas_docs/proc/zsqlex17.htm" target="_blank" >Example 15: Counting Missing Values with a SAS Macro</A> </TD></TR></TABLE>

谢谢
3# hanszhu

hanszhu 发表于 2005-4-5 12:48
Section: SAS SQL:Top 10 SAS SQL examples:http://www2.sas.com/proceedings/sugi29/042-29.pdf A great simple SAS SQL teaching documenthttp://www.pauldickman.com/teaching/sas/proc_sql_code_20041026.pdf A nice article about INTO Clausehttp://www.ats.ucla.edu/stat/sas/library/nesug98/p193.pdf Undocumented and Hard to find SQL Featureshttp://www2.sas.com/proceedings/sugi28/019-28.pdf Part of this new book - Proc SQL: Beyond the Basics Using SAS - By Kirk Paul Lafler where a great collection of codes (To make sense you may benefit well buying the book - look at SAS site for the book)http://www.sas.com/samples/A58316

So nice!

Thanks!