Sample 25455: Demonstrates basic SQL UPDATE statements
 |  |  |  |  |  |
The use of the UNDO_POLICY=NONE produces a WARNING, in the SAS log,
indicating that the option has been overridden
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
This sample is from the SAS Sample Library. For additional information refer to SAS Help and Online Documentation.
/****************************************************************/
/* S A S S A M P L E L I B R A R Y */
/* */
/* NAME: SQLUPDTE */
/* TITLE: Demonstrates Basic SQL UPDATE Statements */
/* PRODUCT: BASE */
/* SYSTEM: ALL */
/* KEYS: SQL DATMAN UPDATE RESET UNDO_POLICY SET WHERE */
/* PROCS: SQL */
/* DATA: */
/* SUPPORT: UPDATE: */
/* REF: */
/* MISC: */
/* */
/****************************************************************/
/* The update statement updates SAS datasets in-place. */
/* The values for the updated variables can be specified as */
/* 1) constant values. */
/* 2) the results of an SQL subquery. */
/* 3) expressions involving the original value. */
/* The first step is to create the Paper table which will be */
/* used in the following queries. */
data paper;
input author$1-8 section$9-16 title$17-43 @45 time time5.
duration;
format time time5.;
label title='Paper Title';
cards;
Tom Testing Automated Product Testing 9:00 35
Jerry Testing Involving Users 9:50 30
Nick Testing Plan to test, test to plan 10:30 20
Peter Info SysArtificial Intelligence 9:30 45
Paul Info SysQuery Languages 10:30 40
Lewis Info SysQuery Optimisers 15:30 25
Jonas Users Starting a Local User Group 14:30 35
Jim Users Keeping power users happy 15:15 20
Janet Users Keeping everyone informed 15:45 30
Marti GraphicsMulti-dimensional graphics 16:30 35
Marge GraphicsMake your own point! 15:10 35
Mike GraphicsMaking do without color 15:50 15
Jane GraphicsPrimary colors, use em! 16:15 25
Jost Foreign Language Issues 11:15 .
;
proc sql;
/* Jost does not have a section name. Let's add "Users" as */
/* his section name. */
update paper set section='Users' where author='Jost';
title2 'After updating section for Jost';
select * from paper;
/* Jost thinks his presentation will last about as long as */
/* everyone elses. Here we us an SQL update statement to */
/* set the duration for Josts paper to the average for all */
/* papers. */
/* The default value of the UNDO_POLICY option will */
/* cause PROC SQL to obtain exclusive access to the */
/* dataset being inserted into (sql.newprice). */
/* The second reference to the same table will fail. */
/* Choosing UNDO_POLICY=OPTIONAL allows this query to proceed. */
/* For details on the UNDO_POLICY option, please refer to the */
/* 607 changes and enhancements documentation. */
reset undo_policy=optional;
update paper
set duration = ( select avg(duration) from paper )
where author = 'Jost';
reset undo_policy=required;
title2 'After updating duration for Jost to AVG(duration)';
select * from paper where author='Jost';
/* Since everyone wants to sleep in, each paper will be */
/* presented 30 minutes later than originally scheduled. */
update paper
set time = time + '0:30't;
title2 'After adding 30 minutes to the start times';
select * from paper;
quit;
These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.
After updating section for Jost author section Paper Title time duration ---------------------------------------------------------------- Tom Testing Automated Product Testing 9:00 35 Jerry Testing Involving Users 9:50 30 Nick Testing Plan to test, test to plan 10:30 20 Peter Info Sys Artificial Intelligence 9:30 45 Paul Info Sys Query Languages 10:30 40 Lewis Info Sys Query Optimisers 15:30 25 Jonas Users Starting a Local User Group 14:30 35 Jim Users Keeping power users happy 15:15 20 Janet Users Keeping everyone informed 15:45 30 Marti Graphics Multi-dimensional graphics 16:30 35 Marge Graphics Make your own point! 15:10 35 Mike Graphics Making do without color 15:50 15 Jane Graphics Primary colors, use em! 16:15 25 Jost Users Foreign Language Issues 11:15 . After updating duration for Jost to AVG(duration) author section Paper Title time duration ---------------------------------------------------------------- Jost Users Foreign Language Issues 11:15 30 After adding 30 minutes to the start times author section Paper Title time duration ---------------------------------------------------------------- Tom Testing Automated Product Testing 9:30 35 Jerry Testing Involving Users 10:20 30 Nick Testing Plan to test, test to plan 11:00 20 Peter Info Sys Artificial Intelligence 10:00 45 Paul Info Sys Query Languages 11:00 40 Lewis Info Sys Query Optimisers 16:00 25 Jonas Users Starting a Local User Group 15:00 35 Jim Users Keeping power users happy 15:45 20 Janet Users Keeping everyone informed 16:15 30 Marti Graphics Multi-dimensional graphics 17:00 35 Marge Graphics Make your own point! 15:40 35 Mike Graphics Making do without color 16:20 15 Jane Graphics Primary colors, use em! 16:45 25 Jost Users Foreign Language Issues 11:45 30
This example demonstrates basic SQL UPDATE statements.
| Type: | Sample |
| Topic: | SAS Reference ==> Procedures ==> SQL |
| Date Modified: | 2005-08-27 03:03:17 |
| Date Created: | 2005-05-23 13:54:59 |
Operating System and Release Information
| Product Family | Product | Host | SAS Release | |
| Starting | Ending | |||
| SAS System | Base SAS | All | n/a | n/a |