Sentry Page Protection
Proc SQL II [14-17]

Updating Tables
In addition to inserting rows into the SAS tables, you can also update the data values in existing rows.

Let's look at an example.​

Copy and run the code from the yellow box below:

Moo Frites is a specialty frites restaurant located in Toronto, Ontario.

The MENU table contains the list of food items offered by Moo Frites.

Note: the prices for the small, medium and large Frites items are $4, $5.95 and $7.95, respectively.

The province of Ontario has decided to raise the minimum wage by $2.

This has significantly affected the profit margin of restaurant owners.

The owner of Moo Frites has decided to raise the price for the regular frites by $0.5.

Below is the Proc SQL step that adjusts the price in the MENU table:
Proc sql;
create table menu_new
as select * from menu;

update menu_new
set price = price+0.5
where item = "Frites";
quit;

We will first copy the MENU table into the MENU_NEW table:

The UPDATE statement tells SAS to update the MENU_NEW table:

The SET clause updates the price by an increment of 0.5:

The WHERE clause limits the update to only the regular Frites item:

The prices on the MENU_NEW table are updated.

The prices for the regular Frites item went up by $0.5. The remaining items have not changed:

Let's look at another example.

The owner of Moo Frites has decided to also raise the price for the following items:​
  • Regular Frites: $0.5
  • Sauce: $0.2
  • Poutine: $1
  • Kimchi Frites: $0.8

You can use the CASE expression to make all the price adjustments in the UPDATE statement.

Example
Proc sql;
create table menu_new
as select * from menu;

update menu_new
set price = price+
  case 
    when item = "Frites"
      then 0.5
    when item = "Sauce"
      then 0.2
    when item = "Poutine"
      then 1
    when item = "Kimchi Frites"
      then 0.8
    else 0
  end;
quit;

The CASE expression allows you to specify the conditions on how the values are updated.

The item prices are now adjusted in the MENU_NEW table based on the conditions specified in the CASE expression:

IMPORTANT

It is highly recommended to include the ELSE clause when using the CASE expression:

Without the ELSE clause, the values that are not defined in the CASE expression will all be set as missing.

Example
Proc sql;
create table menu_new
as select * from menu;

update menu_new
set price = price+
  case 
    when item = "Frites"
      then 0.5
    when item = "Sauce"
      then 0.2
    when item = "Poutine"
      then 1
    when item = "Kimchi Frites"
      then 0.8
  end;
quit;

In this example, the ELSE clause is removed:

The prices for the items that are not included in the CASE expression are all set as missing:
Exercise

Write a Proc SQL step to raise the price on the Japo Frites and War Frites by $2.

Also, insert a new row that contains the following item:
  • ITEM: Pop Drink
  • SIZE: N
  • PRICE: $1.5
Need some help? 


HINT:
You can use two separate statements to update the item prices and insert a new row into the table.


SOLUTION:

Proc sql;
create table menu_new
as select * from menu;

update menu_new
set price = price+
case
when item = "Frites"
then 0.5
when item = "Sauce"
then 0.2
when item = "Poutine"
then 1
when item = "Kimchi Frites"
then 0.8
when item in ("Japo Frites" "War Frites")
then 2
else 0
end;

insert into menu_new (item, size, price)
values ("Pop Drink", "N", 1.5);
quit;

Fill out my online form.