This tutorial explains how to insert or add rows in the same table. It can be easily done with INSERT INTO statement of PROC SQL.
The following SAS program creates a dataset which will be used to explain examples in this tutorial.
data temp; set sashelp.class; run;
With the VALUES clause and INSERT statement, we can assign values to columns by their positions. In the example below, "Sam" would be added to the first column, "M" added to the second column, "28" added to the third column and so on. Multiple VALUES clauses implies multiple rows to be added into the table.
PROC SQL; INSERT INTO temp VALUES ("Sam","M",28,75,100) VALUES ("Sam2","M",58,55,70); QUIT;
See the log shown in the image below -
We can also define columns and values assigned to them only. Values of all the columns that are not defined would be assigned missing.
PROC SQL; INSERT INTO temp (name,sex) VALUES ("Sam","M"); QUIT;
We can also add rows with a query. In the example below, we are appending rows to the table by extracting data from the other table.
proc sql; insert into newclass select * from class where score > 150; quit;
The DATALINES statement with an INPUT statement in DATA STEP is used to read data that you enter directly in the program. In PROC SQL, you can do the same with CREATE TABLE and INSERT INTO statement.
proc sql; create table list (ID num(10), Gender char(1),Salary num, DateOfBirth num informat=date7. format=date7.); insert into list values(12345,'F',42260,'21JAN01'd) values(23456,'M',61090,'26JAN54'd); quit;
We are adding constraints that values of ID variable should be unique (Primary Key), "area" variable contain only two values - USA and India, samplesize should be greater than 0.
proc sql; create table example (ID num(15), samplesize num, area char(15) NOT NULL, constraint prim_key primary key(ID), constraint samplesize check(samplesize gt 0), constraint area check(area in ('USA', 'India'))); quit;
proc sql; insert into example values(12345,42260,'India') values(12345,61090,'USA'); quit;
It returns error due to duplicate values in a variable that have a constraint of primary key.
To check the table, we can display the structure of table using the DESCRIBE TABLE
statement which is an alternative to PROC CONTENTS. In simple words, we are interested to see how table was created and the format of variables.
PROC SQL; DESCRIBE TABLE EXAMPLE; QUIT;
Very good written article with well presented illustrations. This will surely help me in my job as I use proc Sal consistently in my programs. I really appreciate the context of this article and thank you.
ReplyDeleteGlad you found it useful. Cheers!
DeleteVery good article with simple examples. Thank you for posting this.
ReplyDeleteThank you for stopping by my blog.
DeleteHi Deepanshu,
ReplyDeleteI really appreciate your work and way of presenting the things in simple way.
Regards,
- Nikhil
Thank you for your appreciation.
DeleteLove the presentation of the entire site. Makes things extremely easy to understand. Keep up the good work!
ReplyDeleteThese blogs make learning SAS easy.
ReplyDeleteI have a question if you dont mind. When i tried to insert this 'Unit (%)', the result i got is 'Unit ()' without the '%'. Is it because it could read % as a character?
ReplyDeleteIt was helpful. Thanks.
ReplyDeleteGlad you found it helpful. Cheers!
Delete