This week's SAS tip is from Kirk Lafler and his book PROC SQL: Beyond the Basics Using SAS. A SAS user since 1979, Kirk often takes his expertise on the road and is a frequent speaker at SAS conferences. He's also currently working on a second edition of the book.
If you're interested in PROC SQL, visit Kirk's author page for bonus content including a free chapter, samples and tips, user reviews, and more information about his upcoming book. Also, view his previously featured tips on this blog: Creating custom SAS functions with the FCMP Procedure and Displaying integrity constraints.
The following excerpt is from SAS Press author Kirk Lafler and his book "PROC SQL: Beyond the Basics Using SAS." Copyright © 2004, SAS Institute Inc., Cary, North Carolina, USA. ALL RIGHTS RESERVED. (please note that results may vary depending on your version of SAS software)
4 Comments
Thanks for the tip Kirk. Yes, I agree. INSERT TO is a great way to build a dataset containing, for example, data-related issues with your dataset.
I believe using Create table statement instead of Into will be more efficient ? Any comments ..
Your statement on "create table" is right for a table that doesn't exist. For an existing table however, "insert into" is correct.
As Divyesh mentions, a CREATE TABLE statement is most often used to load data in a table that doesn't currently exist, but when the table does exist an INSERT INTO is used.
Another thing to consider when an INSERT INTO or CREATE TABLE statement is used to load large amounts of data containing values that might cause integrity or check constraints to be violated, or one or more values being loaded is too big for the column(s) they are to be loaded into. In these situations violations force a rollback to occur. Although the table is being protected from the entry of "bad" data, performance is most often adversely affected.