Master & Transaction Logic: The Definitive Guide to Applying Sequential Updates in SAS

Difficulty Level

Beginner

Published on : 14/01/2022

Expert Advice

Michael
Responsable de l'infrastructure Viya.

The UPDATE statement is a specialized transaction-processing tool in SAS, designed to merge a "Master" dataset with a "Transaction" dataset. Unlike the MERGE statement, which simply overwrites or combines columns, UPDATE is built with specific business logic for data persistence, ensuring that your master records are refreshed only with relevant, non-missing information.

The UPDATE statement is a powerful tool for dataset maintenance. It requires a BY statement and that the input datasets be sorted or indexed by the BY variables. The flexibility of the UPDATE statement allows adding, deleting, or renaming variables in the output dataset. By default, missing values in the transaction dataset do not replace existing values in the master dataset, but this behavior can be changed with the UPDATEMODE=NOMISSINGCHECK option. It is crucial to ensure that BY variable values are unique in the master dataset, as duplicates can lead to partial updates.

Data Analysis

Type : CREATION_INTERNE

Examples use generated data (datalines) or SASHELP.

1 Code Block

DATA STEP Data

Explanation :
This example illustrates updating a master dataset ('master') with new values from a transaction dataset ('plantNew') using the UPDATE statement and the 'common' variable as the BY variable. The value 'Eggplant' for 'plant' in line 5 of 'master' is replaced by 'Escarole' from 'plantNew'.

Copied!

1	DATA master;
2	INPUT common $ animal $ plant $;
3	DATALINES;
4	a Ant Apple
5	b Bird Banana
6	c Cat Coconut
7	d Dog Dewberry
8	e Eagle Eggplant
9	f Frog Fig
10	;
11	RUN;
12
13	DATA plantNew;
14	INPUT common $ plant $;
15	DATALINES;
16	a Apricot
17	b Barley
18	c Cactus
19	d Date
20	e Escarole
21	f Fennel
22	;
23	RUN;
24
25	DATA master2;
26	update master plantNew;
27	BY common;
28	RUN;
29	PROC PRINT DATA=master2; RUN;

2 Code Block

DATA STEP Data

Explanation :
This example demonstrates the behavior of the UPDATE statement when the transaction dataset ('plantNewDupes') contains duplicate values for the BY variable ('common'). The value 'Dewberry' is replaced by 'Dill' because 'Dill' is the last value for 'plant' associated with 'd' in the transaction dataset. It is important to note that if the master dataset contains duplicates for the BY variable, only the first occurrence is updated, and a warning is issued in the SAS log.

Copied!

1	DATA master;
2	INPUT common $ animal $ plant $;
3	DATALINES;
4	a Ant Apple
5	b Bird Banana
6	c Cat Coconut
7	d Dog Dewberry
8	e Eagle Eggplant
9	f Frog Fig
10	;
11	RUN;
12
13	DATA plantNewDupes;
14	INPUT common $ plant $;
15	DATALINES;
16	a Apricot
17	b Barley
18	c Cactus
19	d Date
20	d Dill
21	e Escarole
22	f Fennel
23	;
24	RUN;
25
26	DATA master;
27	update master plantNewDupes;
28	BY common;
29	RUN;
30	PROC PRINT DATA=master; RUN;

3 Code Block

DATA STEP Data

Explanation :
This first code illustrates updating a master dataset ('master') with a transaction dataset ('minerals') that introduces a new variable ('mineral'), missing values, and non-matching BY variable values. The 'mineral' variable is added. Observations from the transaction dataset without a match in the master are added as new observations. Missing values in the transaction dataset do not replace existing values in the master dataset by default.

Copied!

1	DATA master;
2	INPUT common $ animal $ plant $;
3	DATALINES;
4	a Ant .
5	c Cat Coconut
6	d Dog Dewberry
7	e Eagle Eggplant
8	f Frog Fig
9	;
10	RUN;
11
12	DATA minerals;
13	INPUT common $ plant $ mineral $;
14	DATALINES;
15	a Apricot Amethyst
16	b Barley Beryl
17	c Cactus .
18	e . .
19	f Fennel .
20	g Grape Garnet
21	;
22	RUN;
23
24	DATA master;
25	update master minerals;
26	BY common;
27	RUN;
28
29	PROC PRINT DATA=master; RUN;

4 Code Block

DATA STEP

Explanation :
This second code, based on the datasets from example 3.1, shows how the UPDATEMODE=NOMISSINGCHECK option allows missing values in the transaction dataset to replace corresponding values in the master dataset. Here, the 'plant' value in observation 5 will be set to missing because it is missing in the 'minerals' transaction dataset and the option is enabled.

Copied!

1	DATA master;
2	update master minerals updatemode=nomissingcheck;
3	BY common;
4	RUN;
5	PROC PRINT DATA=master;
6	title "Updated Data Set master";
7	title2 "With Values Updated to Missing";
8	RUN;

Pro Tip

Unlike the MERGE statement which can handle many-to-many relationships, the UPDATE statement strictly requires that your Master dataset contains unique values for the BY variable; if the Master dataset has duplicate keys, only the first observation for that key is updated, whereas the Transaction dataset is allowed to have duplicate keys which are applied sequentially to that single Master record.

This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.

Difficulty Level

Published on : 14/01/2022

Expert Advice

Data Analysis

1 Code Block

2 Code Block

3 Code Block

4 Code Block

Pro Tip

Related Documentation