SAS Chapter 15: Combining Data Horizontally

1. Suppose you want to merge Dataset1, Dataset2, and Dataset3. Also suppose that Dataset1 and Dataset2 have the common variableStartdate, Dataset2 and Dataset3 have the common variable Instructor, and that these data sets have no other common variables. How can you use a DATA step to merge these three data sets into one new data set?

You use a MERGE statement in one DATA step to merge Dataset1, Dataset2, and Dataset3 by Startdate and Instructor.

You sort Dataset1 and Dataset2 by Startdate and merge them into a temporary data set in a DATA step. Then you sort the temporary data set and Dataset3 by Instructor and merge them into a new data set in a DATA step.

You can merge these data sets only with a PROC SQL step.

You cannot merge these three data sets at all because they do not have a common variable.

In order to merge multiple data sets in a DATA step, the data sets must have a common variable. However, if there are variables that are common to at least two of the input data sets, and if each input data set contains at least one of these variables, then you can use subsequent DATA steps to merge the data sets. You can also use a PROC SQL step to merge data sets that do not have common variables.

Explanation

In order to merge multiple data sets in a DATA step, the data sets must have a common variable. However, if there are variables that are common to at least two of the input data sets, and if each input data set contains at least one of these variables, then you can use subsequent DATA steps to merge the data sets. You can also use a PROC SQL step to merge data sets that do not have common variables.

2. Select the program that correctly creates a new data set named Sasuser.Summary that contains one observation with summary data created from the Salary variable of the Sasuser.Empdata data set.

Proc sum data=sasuser.emdata noprint; output out=sasuser.summary sum=Salarysum;run;

Proc means data=sasuser.empdata noprint; var salary; output out=sasuser.summary sum=Salarysum;run;

Proc sum data=sasuser.empdata noprint; var salary; output out=sasuser.summary sum=Salarysum;run;

Proc means data=sasuser.empdata noprint; output=sasuser.summary sum=Salarysum;run;

You can use the MEANS procedure to create a new data set that contains a summary statistic. You use the NOPRINT option to suppress the default report and the OUTPUT statement to route the results from the MEANS procedure to a new data set. You use the VAR statement to focus the procedure on one or more specific variables from the input data set.

Explanation

You can use the MEANS procedure to create a new data set that contains a summary statistic. You use the NOPRINT option to suppress the default report and the OUTPUT statement to route the results from the MEANS procedure to a new data set. You use the VAR statement to focus the procedure on one or more specific variables from the input data set.

3. According to the data set descriptions below, which of the variables listed qualify as BY variables for a DATA step match-merge?

Code and IDnum

Manager and Supervisor

Manager and IDnum

There are no variables that are common to both of these data sets.

Remember that common variables might not have the same names. Manager and IDnum are the only two variables listed that match according to type and description. You can use the RENAME= option to rename one of these variables so that they can be used as BY variables in the MERGE statement of the DATA step.

Explanation

Remember that common variables might not have the same names. Manager and IDnum are the only two variables listed that match according to type and description. You can use the RENAME= option to rename one of these variables so that they can be used as BY variables in the MERGE statement of the DATA step.

4. Which of the following statements about the _IORC_ variable is false?

It is automatically created when you use either a SET statement with the KEY= option or the MODIFY statement with the KEY= option in a DATA step.

A value of zero for _IORC_ means that the most recent SET statement with the KEY= option (or MODIFY statement with the KEY= option) did not execute successfully.

A value of zero for _IORC_ means that the most recent SET statement with the KEY= option (or MODIFY statement with the KEY= option) executed successfully.

You can use the _IORC_ variable to prevent nonmatching data from being included when you use an index to combine data from multiple data sets.

When you use the KEY= option, SAS creates an automatic variable named _IORC_, which stands for INPUT/OUTPUT Return Code. If the value of _IORC_ is zero, the index search was successful. The _IORC_ variable is also created automatically when you use a MODIFY statement in a DATA step.

Explanation

When you use the KEY= option, SAS creates an automatic variable named _IORC_, which stands for INPUT/OUTPUT Return Code. If the value of _IORC_ is zero, the index search was successful. The _IORC_ variable is also created automatically when you use a MODIFY statement in a DATA step.

5. Which of the following programs correctly creates a table with the results of a PROC SQL inner join matched on the values of empcode?

Proc sql; select newsals.empcode allemps.lastname newsals.salary contrib.amount from sasuser.allemps, sasuser.contrib, sasuser.newsals where empcode=allemps.empid and empcode=contrib.empid;quit;

Proc sql; create table usesql as select newsals.empcode allemps.lastname newsals.salsry contrib.amount from sasuser.allemps, sasuser.contrib, sasuser.newsalsquit;

Proc sql; create table usesql as; select newsals.empcode, allemps.lastname, newsals.salary, contrib.amount; from sasuser.allemps, sasuser.contrib, sasuser.newsals; where empcode=allemps.empid and empcode=contrib.empid;quit;

Proc sql; create table usesql as select newsals.empcode, allemps.lastname, newsals.salary, contrib.amount from sasuser.allemps, sasuser.contrib, sasuser.newsals where empcode=allemps.empid and empcode=contrib.empid;quit;

You can use PROC SQL to join data from data sets that do not have a single common variable among them. If you create a new table with the result of an inner join in a PROC SQL step, the resulting data set can be similar or identical to the result of a DATA step match-merge.

Explanation

You can use PROC SQL to join data from data sets that do not have a single common variable among them. If you create a new table with the result of an inner join in a PROC SQL step, the resulting data set can be similar or identical to the result of a DATA step match-merge.

6. To process a default DATA step match-merge, SAS first reads the descriptor portion of each data set and sets up the PDV and the descriptor portion of the new data set. Which of the following accurately describes the rest of this process?

Next, SAS sequentially match-merges observations and writes the new observation to the PDV, then to the new data set. When the BY value changes in all the input data sets, the PDV is initialized to missing. Missing values for variables, as well as missing values that result from unmatched observations, are written to the new data set.

Next, SAS sequentially match-merges observations and writes the new observation to the PDV, then to the new data set. After each DATA step iteration, the PDV is initialized to missing. Missing values for variables, as well as missing values that result from unmatched observations, are omitted from the new data set.

Next, SAS creates a Cartesian product of all possible combinations of observations and writes them to the PDV, then to the new data set. Then SAS goes through the new data set and eliminates all observations that do not have matching values of the BY variable.

Next, SAS creates a Cartesian product of all possible combinations of observations and writes them to the PDV, then to the new data set. The new data set is then ordered by values of the BY variable.

In a DATA step match-merge, SAS reads observations from the input data sets sequentially and match-merges them with observations from other input data sets. Combined observations are created when SAS writes values from all input data sets to the variables in the PDV. These observations, as well as any observations that contain missing or nonmatched values, are then written to the new data set. A PROC SQL join creates a Cartesian product of matches and then eliminates nonmatching data.

Explanation

In a DATA step match-merge, SAS reads observations from the input data sets sequentially and match-merges them with observations from other input data sets. Combined observations are created when SAS writes values from all input data sets to the variables in the PDV. These observations, as well as any observations that contain missing or nonmatched values, are then written to the new data set. A PROC SQL join creates a Cartesian product of matches and then eliminates nonmatching data.

7. Which of the following statements is false about using multiple SET statements in one DATA step?

You can use multiple SET statements to combine observations from several SAS data sets.

Processing stops when SAS encounters the end-of-file (EOF) marker on either data set (even if there is more data in the other data set).

You can use multiple SET statements in one DATA step only if the data sets in each SET statement have a common variable.

The variables in the PDV are not reinitialized when a second SET statement is executed.

You can use multiple SET statements in one DATA step to combine observations from several data sets, and the data sets do not need to have a common variable. When you use multiple SET statements, you need to keep in mind the process that SAS uses to combine data from the input data sets. Otherwise, you might achieve unexpected results.

Explanation

You can use multiple SET statements in one DATA step to combine observations from several data sets, and the data sets do not need to have a common variable. When you use multiple SET statements, you need to keep in mind the process that SAS uses to combine data from the input data sets. Otherwise, you might achieve unexpected results.

8. According to the data set shown, what is the value of Totalrev in the PDV at the end of the fourth iteration of the DATA step?

The value is missing.

$350.00

$520.00

$1100.00

Totalrev is the accumulator variable of the sum statement, which is automatically initialized with a value of 0. If the expression in a sum statement produces a missing value, SAS replaces the missing value with a value of 0. As the DATA step iterates, the sum statement retains the accumulator variable so that it will accumulate a total.

Explanation

Totalrev is the accumulator variable of the sum statement, which is automatically initialized with a value of 0. If the expression in a sum statement produces a missing value, SAS replaces the missing value with a value of 0. As the DATA step iterates, the sum statement retains the accumulator variable so that it will accumulate a total.

9. If the value of Cargosum is $1000 at the end of the first iteration of the DATA step shown below, what is the value of Cargosum in the PDV when the DATA step is in its third iteration?

data sasuser.percent1;
   if _n_=1 then set sasuser.summary (keep=cargosum);
   set sasuser.monthsum (keep=salemon revcargo);
   PctRev=revcargo/cargosum;
run;

$1000

$3000

The value is missing.

The value cannot be determined without seeing the data that is in Sasuser.Summary.

The _N_ variable records how many times the DATA step has iterated. In the example shown above, _N_ is used to ensure that only the first observation is read from Sasuser.Summary. Since the values in the PDV are not reinitialized after each DATA step iteration, this value will be retained as long as the DATA step continues to iterate. Therefore, if the value of Cargosum is $1000 in the first iteration, it will be $1000 in each subsequent iteration as well.

Explanation

The _N_ variable records how many times the DATA step has iterated. In the example shown above, _N_ is used to ensure that only the first observation is read from Sasuser.Summary. Since the values in the PDV are not reinitialized after each DATA step iteration, this value will be retained as long as the DATA step continues to iterate. Therefore, if the value of Cargosum is $1000 in the first iteration, it will be $1000 in each subsequent iteration as well.

10. Which of the following programs correctly uses an index to combine data from two input data sets?

Data work.profit; set sasuser.sale2000(keep=routeid flightid date rev1st revbusiness revecon revcargo) key=flightdate; set sasuser.dnunder; Profit=sum(rev1st, revbusiness, revecon, revcargo, -expenses);run;

Data work.profit; set sasuser.dnunder; set sasuser.sale2000(keep=routeid flightid date rev1st revbusiness revecon revcargo) key=flightdate; where routeid='0000103'; Profit=sum(rev1st, revbusiness, revecon, revcargo, -expenses);run;

Data work.profit; set sasuser.dnunder; set sasuser.sale2000(keep=routeid flightid date rev1st revbusiness revecon revcargo); key=flightdate; Profit=sum(rev1st, revbusiness, revecon, revcargo, -expenses);run;

Data work.profit; set sasuser.dnunder; set sasuser.sale2000(keep=routeid flightid date rev1st revbusiness revecon revcargo) key=flightdate; Profit=sum(rev1st, revbusiness, revecon, revcargo, -expenses);run;

You use the KEY= option in a SET statement to cause SAS to use an index to combine data from multiple data sets. When the SET statement with the KEY= option executes, the program data vector must already contain a value for the indexed variable. You cannot use WHERE processing on a data set that has been read with the KEY= option within the same DATA step.

Explanation

You use the KEY= option in a SET statement to cause SAS to use an index to combine data from multiple data sets. When the SET statement with the KEY= option executes, the program data vector must already contain a value for the indexed variable. You cannot use WHERE processing on a data set that has been read with the KEY= option within the same DATA step.