SAS: Using UPDATE in PROC SQL

In PROC SQL, the UPDATE statement is used to modify existing values of columns in a table.

The syntax of the UPDATE statement is as follows :

PROC SQL;
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
QUIT;

Sample Dataset

Let's create a sample SAS dataset to explain examples in this tutorial.

data sample_table;
set sashelp.cars;
run;

Simple Example of UPDATE Statement

In this example, we are telling SAS to update the 'make' column and set it to "Tesla" when the MSRP column is greater than $50,000.

PROC SQL;
UPDATE sample_table
SET make = 'Tesla'
WHERE msrp > 50000;
QUIT;

To confirm if the UPDATE statement was performed correctly, you can use the PROC PRINT procedure and check the updated records.

proc print data=sample_table;
var make msrp;
WHERE msrp > 50000;
run;

How to Update Multiple Columns

The following code updates the "model" column to 'Model S' and the "Type" column to 'Sedan' in the "sample_table" table for rows where the "msrp" is greater than 80,000 and the "make" is 'Tesla'.

/* Update sample_table */
PROC SQL;
UPDATE sample_table
SET model = 'Model S', Type = 'Sedan'
WHERE msrp > 80000 and make = 'Tesla';
QUIT;

/* Validate the changes */
proc print data=sample_table;
var make msrp model type;
WHERE msrp > 80000 and make = 'Tesla';
run;

About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn