units=units*4
Method of Updating Table
|
Example
|
---|---|
update all (or a subset
of) rows in a column with the same expression
|
proc sql;
update work.payrollmaster_new
set salary=salary*1.05
where jobcode like '__1'; |
update different rows
in a column with different expressions
|
proc sql;
update work.payrollmaster_new
set salary=salary*
case when substr(jobcode,3,1)='1'
then 1.05
when substr(jobcode,3,1)='2'
then 1.10
when substr(jobcode,3,1)='3'
then 1.15
else 1.08
end; |
General form, basic
UPDATE statement for updating table rows:
UPDATE table-name
SET column-1=expression<,
... column-n=expression>>
<WHERE expression>;
table-name
specifies the name
of the table in which values are updated.
SET
specifies one or more
pairs of column names to be
updated, and expressions that
indicate how each column is to be updated.
WHERE
is used to specify
an expression that subsets
the rows to be updated.
|
proc sql; create table work.payrollmaster_new as select * from sasuser.payrollmaster; select * from work.payrollmaster_new;
proc sql; update work.payrollmaster_new set salary=salary*1.05 where jobcode like '__1';
proc sql; update work.payrollmaster_new set salary=salary*1.05;
Method of Updating Table
|
Example
|
---|---|
use multiple UPDATE
statements subset of rows
A single UPDATE statement
can contain only a single WHERE clause, so multiple UPDATE statements
are needed to specify expressions for multiple subsets of rows.
|
proc sql; update work.payrollmaster_new set salary=salary*1.05 where substr(jobcode,3,1)='1'; update work.payrollmaster_new set salary=salary*1.10 where substr(jobcode,3,1)='2'; update work.payrollmaster_new set salary=salary*1.15 where substr(jobcode,3,1)='3'; |
use a single UPDATE
statement that contains a CASE expression
|
proc sql; update work.payrollmaster_new set salary=salary* case when substr(jobcode,3,1)='1' then 1.05 when substr(jobcode,3,1)='2' then 1.10 when substr(jobcode,3,1)='3' then 1.15 else 1.08 end; |
General form, CASE expression:
CASE <case-operand>
WHEN when-condition THEN result-expression
<...WHEN when-condition THEN result-expression>
<ELSE result-expression>
END;
CASE
performs conditional
processing.
case-operand
is an optional expression
that resolves to a table column whose values are compared to all the when-conditions.
WHEN
specifies a when-condition,
a shortened expression that assumes case-operand as
one of its operands, and that resolves to true or false.
THEN
specifies a result-expression,
an expression that resolves to a value.
ELSE
specifies a result-expression,
which provides an alternate action if none of the when-conditions is
executed.
END
indicates the end of
the CASE expression.
|
update work.insure_new set pctinsured=pctinsured* case when company='ACME' then 1.10 when company='RELIABLE' then 1.15 when company='HOMELIFE' then 1.25 else 1 end;
proc sql; update work.payrollmaster3 set salary=salary* case when substr(jobcode,3,1)='1' then 1.05 when substr(jobcode,3,1)='2' then 1.10 when substr(jobcode,3,1)='3' then 1.15 else 1.08 end;
proc sql; update work.payrollmaster_new2 set salary=salary* case when substr(jobcode,3,1)='1' then 1.05 when substr(jobcode,3,1)='2' then 1.10 when substr(jobcode,3,1)='3' then 1.15 else 1.08 end;
proc sql;
update work.payrollmaster_new2
set salary=salary*
case substr(jobcode,3,1)
when '1'
then 1.05
when '2'
then 1.10
when '3'
then 1.15
else 1.08
end;
1
, 2
,
or 3
.) To create JobLevel, you can
use the case operand form of the CASE expression to specify the three
possible conditions (plus an ELSE condition, just in case).
proc sql outobs=10; select lastname, firstname, jobcode, case substr(jobcode,3,1) when '1' then 'junior' when '2' then 'intermediate' when '3' then 'senior' else 'none' end as JobLevel from sasuser.payrollmaster, sasuser.staffmaster where staffmaster.empid= payrollmaster.empid; |
|
junior
, intermediate
, senior
,
or none
to each row in the new JobLevel
column.