The UPDATE statement is designed to update a master data set from atransaction data set.An example of a master data set would be a data set containing all employee records or a data set of product numbers, descriptions, inventory levels,and prices.
A typical transaction data set would contain only the information to identify the record to be updated and the new updated information; for example,a product number and the new price.An update would be accomplished by substituting the new information in the transaction file for the old information in the master file.
One main difference between the UPDATE and MERGE statements is that if there is a missing value for a variable in the transaction data set,that variable is untouched in the master data set. Things can get messy when there are multiple values of the BY variable(s) in either the master data set,the transaction data set,or both, so we will stick to basic examples.
Suppose you have a master and a transaction data set, both of which contain the variables ID, DEPT, and SALARY.Contents of the data sets are as follows:
You want to apply the transaction data set to the master data set to update the records. If you assume the records are already sorted by ID,you can write:
The syntax is obvious.The new data set created, NEWMAST,is the old data set, MASTER,updated with the data from TRANS.Output is as follows:
Output from Example-Updating a Master File from a Transaction File
Notice that observations 1 and 4 in the original MASTER data set are unchanged since these ID's are not present in the TRANS data set.In observation 2, the value for SALARY in the TRANS data set (22,000)replaced the corresponding value in the MASTER data set (21,000).
The missing value for DEPT in observation 2 of the TRANS data set did not, however,replace the corresponding value for DEPT in the MASTER data set.In observation 3, both values in the TRANS data set replaced the corresponding values in the MASTER data set.TRANS data set value of RECORDS for variable DEPT replaced the originally missing value in the MASTER data set, while the missing TRANS data set value for SALARY did not replace the MASTER value (18,000).If a value of the BY variable (ID) is present in the transaction data set which is not present in the master data set, the new observation will be added to the new master data set.
For example,if MASTER and TRANS were as follows:
The resulting data set, NEWMAST, would contain:
f a transaction data set has fewer variables than a master data set,the update process only affects the variables contained in the transaction data set and leaves the remainder of the variables in the master data set unchanged.
Suppose data set TRANS only held data for ID and SALARY, as follows:
The result would be that DEPT would be left unchanged, and SALARY would be updated.Data set NEWMASTID DEPT SALARY
Notice that the new observation,ID=9, is added to NEWMAST.If a transaction data set has more variables than a master data set, the update process adds the additional variables to the updated data set.
Suppose data set TRANS contained data for a new variable, GENDER, in addition to updated data for old variables, as follows:The variable GENDER, along with its values, is added to NEWMAST as follows:Data set NEWMASTID DEPT SALARY GENDER
Until now, you have always been using a transaction data set called TRANS to update a master data set called MASTER,and you have been keeping the updated data in a new data set called NEWMAST.This is not absolutely necessary, however.The following code will update data set MASTER "in place":
There is always some risk of losing data when using this "in place" method of updating, and in a true production environment, you would probably want to use a backup scheme incorporated into your update process.
Data set TWOID SEX DOB SALARY TAXRATE WITHHOLD
Data set THREEID SEX DOB SALARY HEIGHT WEIGHT
Write a program to create a single SAS data set ALL which combines the observationsfrom data sets ONE,TWO,and THREE and contains the variables ID, DOB, and SALARY.A listing of the resulting data set is shown below:
Data set ALL
a) Write a program that will list all the possible names meeting these criteria.
b) Using the same data set,write a program to extract all last names that sound like "Klein" and with first names beginning with'G'followed by 4 other letters.(Yes,we know this sounds ridiculous but we want to give you some practice with the WHERE operators.)
Notes: 1. Data are not in ID order.
2. There are some IDs that are in one file only.
SAS Programming Related Tutorials
|SASS (Syntactically Awesome Style sheets) Tutorial||R Programming language Tutorial|
SAS Programming Related Interview Questions
|Logistics Interview Questions||SAS Programming,SQL server Interview Questions|
|Clinical SAS Interview Questions||SASS (Syntactically Awesome Style sheets) Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||R Programming language Interview Questions|
|SAS DI Interview Questions||Advanced SAS Interview Questions|
|Base Sas Interview Questions||SAS Macro Interview Questions|
|Clinical Data Management Interview Questions|
Sas Programming Tutorial
Input And Infile
Set, Merge, And Update
Table Lookup Tools
Proc Means And Proc Uimivariate
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.