You've learned about creating a SAS data set from raw data. However, you may often want to create a new data set from an existing SAS data set. To create the new data set, you can read a data set using the DATA step. As you read the data set, you can use all the programming features of the DATA step to manipulate your data.

This chapter shows you how to use the DATA step to read an existing SAS data set. When you create your new data set, you can choose variables, select observations based on one or more conditions, and assign values conditionally. You can also assign variable attributes such as formats and labels.

In this chapter, you learn to

The following DATA step reads the data set Research.CLTrials, selects observations and variables, and creates new variables.

data lab23.drug1h(drop=placebo uric);
   set research.cltrials(drop=triglyc);
   if sex='M' then delete;
   if placebo='YES';
   TestDate='22MAY1999'd;
   retain Days 30;
   days+1;
   length Retest $ 5;
   if cholesterol>190 then retest='YES';
   else if 150<=cholesterol<=190 then retest='CHECK';
   else if cholesterol<150 then retest='NO';
   label retest='Perform Cholesterol Test 2?';
   format enddate mmddyy10.;
run;

You've learned that you can specify the DROP= and KEEP= data set options anywhere you name a SAS data set. However, using DROP= and KEEP= when reading an existing data set requires that you decide where to specify these options. You can specify DROP= and KEEP= in either the DATA statement or the SET statement, depending on whether or not you want to process values of the variables in that DATA step:

When used in the DATA statement, the DROP= option simply drops the variables from the new data set. However, they are still read from the original data set and are available within the DATA step.

If you completed Chapter 4, "Creating List Reports," on page 99, you learned to use a BY statement in PROC SORT to sort observations and in PROC PRINT to group observations for subtotals. You can also use the BY statement in the DATA step to group observations for processing.

data temp;
   set salary;
   by dept;
run;

When you use the BY statement with the SET statement,

To work with FIRST.variable and LAST.variable, let's look at a different set of data. The Company.USA data set contains payroll information for individual employees. Suppose you want to compute the annual payroll by department. Assume 2,000 work hours per year for hourly employees.

Before computing the annual payroll, you need to group observations by values of the variable Dept.

The following program computes the annual payroll by department. Notice that the variable name Dept has been appended to FIRST. and LAST.

proc sort data=company.usa out=work.temp;
   by dept;
run;
data company.budget(keep=dept payroll);
   set work.temp;
   by dept;
   if wagecat='S' then Yearly=wagerate*12;
   else if wagecat='H' then Yearly=wagerate*2000;
   if  first.dept then Payroll=0;
   payroll+yearly;
   if  last.dept;
run;

If you could look behind the scenes at the program data vector (PDV) as the Company.Budget data set is being created, you would see the following. Notice the values for FIRST.Dept and LAST.Dept.

When you print the new data set, you can now list and sum the annual payroll by department.

proc print data=company.budget noobs;
   sum payroll;
   format payroll dollar12.2;
run;

When you specify multiple BY variables,

Suppose you now want to compute the annual payroll by job type for each manager. In your program, you specify two BY variables, Manager and JobType.

proc sort data=company.usa out=work.temp2;
   by manager jobtype;

data company.budget2(keep=manager jobtype payroll);
   set work.temp2;
   by manager jobtype;
   if wagecat='S' then Yearly=wagerate*12;
   else if wagecat='H' then Yearly=wagerate*2000;
   if first.jobtype then Payroll=0;
   payroll+yearly;
   if last.jobtype;
run;

If you could look at the PDV now, you would see the following. Notice that the values for FIRST.JobType and LAST.JobType change according to values of FIRST.Manager and LAST.Manager.

Now you can sum the annual payroll by job type for each manager. Here, the payroll for only two managers (Coxe and Delgado) is listed.

proc print data=company.budget2 noobs;;
   by manager;
   var jobtype;
   sum payroll;
   where manager in ('Coxe','Delgado'),
   format payroll dollar12.2;
run;

So far in this chapter, you've read the observations in an input data set sequentially. That is, you have accessed observations in the order in which they appear in the physical file. However, you can also access observations directly, by going straight to an observation in a SAS data set without having to process each observation that precedes it.

To access observations directly by their observation number, you use the POINT= option in the SET statement.

Let's suppose you want to read only the fifth observation from a data set. In the following DATA step, the value 5 is assigned to the variable ObsNum. The POINT= option reads the value of ObsNum to determine which observation to read from the data set Company.USA.

data work.getobs5;
   obsnum=5;
   set company.usa(keep=manager payroll)  point=obsnum;
run;

But let's see what would happen if you submitted this program.

As you learned in a previous chapter, the DATA step continues to read observations until it reaches the end-of-file marker in the input data. However, because the POINT= option reads only specified observations, SAS cannot read an end-of-file indicator as it would if the file were being read sequentially. So submitting the following program would cause continuous looping.

data work.getobs5(drop=obsnum);
   obsnum=5;
   set company.usa(keep=manager payroll)  point=obsnum;
run;

Because there is no end-of-file condition when you use direct access to read data, you must take either or both of the following precautions:

So, if you add a STOP statement, your program no longer loops continuously.

data work.getobs5(drop=obsnum);
   obsnum=5;
   set company.usa(keep=manager payroll)  point=obsnum;
   stop;
run;

But it doesn't write any observations to output, either! Remember from Chapter 6, "Understanding DATA Step Processing," on page 183 that the DATA step writes observations to output at the end of the DATA step. However, in this program, the STOP statement immediately stops processing before the end of the DATA step.

Let's see how you can write the observation to output before processing stops.

To override the default way in which the DATA step writes observations to output, you can use an OUTPUT statement in the DATA step. Placing an explicit OUTPUT statement in a DATA step overrides the automatic output, so that observations are added to a data set only when the explicit OUTPUT statement is executed.

With an OUTPUT statement, your program now writes a single observation to output—observation 5.

data work.getobs5(drop=obsnum);
   obsnum=5;
   set company.usa(keep=manager payroll) point=obsnum;
   output;
   stop;
run;
proc print data=work.getobs5 noobs;
run;

Suppose your DATA statement contains two data set names, and you include an OUTPUT statement that references only one of the data sets. The DATA step will create both data sets, but only the data set that is named in the OUTPUT statement will contain output. For example, the program below creates two temporary data sets, Empty and Full. The result of this DATA step is that the data set Empty is created but contains no observations, and the data set Full contains all of the observations from Company.Usa.

data empty full;
   set company.usa;
   output full;
run;

To convey concepts clearly, the examples in this section have been as simple as possible. However, most uses of the POINT= option are more complex. For example, POINT= is commonly used in combining data sets, not simply in reading a single data set.

You can see more complex examples of using POINT= in Chapter 15, "Generating Data with DO Loops," on page 473.

Suppose you want to sum the number of seconds for treadmill stress tests. If you submit the following program, you produce a new data set that contains cumulative totals for each of the values of TotalTime.

data work.addtoend(drop=timemin timesec);
   set clinic.stress2(keep=timemin timesec);
   TotalMin+timemin;
   TotalSec+timesec;
   TotalTime=totalmin*60+totalsec;
run;
proc print data=work.addtoend noobs;
run;

But what if you only want the final total (the last observation) in the new data set? The following program uses the END= variable last to select only the last observation of the data set. You specify END= in the SET statement and last wherever you need it in processing (here, in the subsetting IF statement).

data work.addtoend(drop=timemin timesec);
   set clinic.stress2(keep=timemin timesec)  end=last;
   TotalMin+timemin;
   TotalSec+timesec;
   TotalTime=totalmin*60+totalsec;
   if last;
run;
proc print data=work.addtoend noobs;
run;

Now the new data set has one observation:

In a previous chapter, you learned about the compilation and execution phases of the DATA step as they pertain to reading raw data. DATA step processing for reading existing SAS data sets is very similar. The main difference is that while reading an existing data set with the SET statement, SAS retains the values of the variables from one observation to the next.

Let's briefly look at the compilation and execution phases of DATA steps that use a SET statement. In this example, the DATA step reads the data set Finance.Loans, creates the variable Interest, and creates the new data set Finance.DueJan.

data finance.duejan;
   set finance.loans;
   Interest=amount*(rate/12);
run;

When the compilation phase is complete, the execution phase begins.

This process continues until all of the observations are read.

LIBNAME libref 'SAS-data-library';

DATA <SAS-data-set> (KEEP= variable-1 <…variable-n>);

      SET SAS-data-set (DROP= variable-1 <…variable-n>)

      POINT=variable | END=variable;

      OUTPUT <SAS-data-set>;

      STOP;

RUN;

proc sort data=company.usa out=work.temp2;
   by manager jobtype;
data company.budget2(keep=manager jobtype payroll);
   set work.temp2;
   by manager jobtype;
   if wagecat='S' then Yearly=wagerate*12;
   else if wagecat='H' then Yearly=wagerate*2000;

   if first.jobtype then Payroll=0;
   payroll+yearly;
   if last.jobtype;
run;


data work.getobs5(drop=obsnum);
   obsnum=5;
   set company.usa(keep=manager payroll) point=obsnum;
   output;
   stop;
run;


data work.addtoend(drop=timemin timesec);
   set clinic.stress2(keep=timemin timesec) end=last;
   TotalMin+timemin;
   TotalSec+timesec;
   TotalTime=totalmin*60+totalsec;
   if last;
run;