PROC SQL PROC SQL provides the combined functionality of the DATA step and several base SAS procedures. Less complex and lengthy, but not as legible, code can be written in PROC SQL. PROC SQL code may execute faster for smaller tables. PROC SQL code is more portable for non-SAS programmers and non-SAS applications. PROC SQL processing does not require explicit code to presort tables. PROC SQL processing does not require common variable names to join on, although same type and length are required. By default, a PROC SQL SELECT statement prints the resultant query; use the NOPRINT option to suppress this feature. Knowledge of relational data theory opens the power of SQL for many additional tasks. PROC SQL processing forces attention to resultant data set structures, as SQL is unforgiving of "errors of design". Efficiencies within specific RDBMS are available with Pass-thru code for the performance of joins. Use of aliases for shorthand code may make some coding tasks easier.
CREATE TABLE table1 ( charvar1 CHAR(3) , charvar2 CHAR(1) , numvar1 NUM , numvar2 NUM INFORMAT=DATE7.) ; INSERT INTO table1 VALUES('me1','F',35786,'10oct50'd) VALUES('me3','M',57963,'25jun49'd) VALUES('fg6','M',25754,'17jun47'd) VALUES('fg7','F',.,'17aug53'd) ; SELECT * FROM table1; QUIT;
SAS programs are made up of distinct steps, and each one is completed before it moves on to the next one. Data steps are written by you. They are primarily used for data manipulation (hence the name) though in theory you could do some sorts of analysis with them. Proc steps are pre-written programs made available as part of SAS. The code may look similar to a data step in some ways, but the code in a proc step is not giving SAS step-by-step instructions to execute. All you are really doing is controlling how the proc step runs. We will use a few simple procs in the course of this article, but for more details see the SAS documentation.
A step starts with either the word data or the word proc, and ends with the word run;. The run; is often not strictly required, as SAS will assume you want to start a new step when it sees data or proc. However your code will be clearer and easier to understand if you make the end of each step explicit. That may not seem very important the first time you work on a particular program, but when you have to come back to it months later and figure out what you did, you'll quickly see that saving a few keystrokes is far less important than writing clear code. Obviously if you will be sharing this code with anyone else then making it easy to understand is even more important.
DATA table2; SET table1(WHERE=(var1=value1)); DATA table4; SET table3 IF var1=value1 AND var2 IN (value-list); DATA table7; MERGE table5 table6; BY var1; IF M0D(var4,3) NE 0 THEN DELETE;
Ready to start your tutorial with us? That's great! Send us an email and we will get back to you as soon as possible!