lec5 student

8/6/2019 Lec5 Student

http://slidepdf.com/reader/full/lec5-student 1/28

Concatenating SAS Data Sets



2

Define concatenation.

Use the SET statement in a DATA step toconcatenate two or more SAS data sets.

Use the RENAME= data set option to change thenames of variables.

Use the SET and BY statements in a DATA step tointerleave two or more SAS data sets.

Objectives



3

DATA SAS-data-set ;SET SAS-data-set1 SAS-data-set2 . . .;<other SAS statements >

RUN;

Concatenating SAS Data SetsUse the SET statement in a DATA step to concatenate

SAS data sets.



4

You can read any number of SAS data sets with a single

SET statement.

SAS data sets

jan

feb

mar

data work.qtr1;set work.jan work.feb

work.mar;

run;

janfeb

mar

work.qtr1

...

Concatenating SAS Data Sets



5

Two SAS data sets, na1 and na2, contain data for newly

hired navigators. Concatenate the data sets into a newdata set named newhires.

Name

Gender

JobCodeTORRES M

NA1

LANG

F

NA1

SMITH F

NA1

Name

Gender JobCodeLISTER M

NA2

TORRES F NA2

na1 na2

Business Task



6

Name

Gender JobCodeTORRES M

NA1

LANG

F NA1

SMITH F NA1

LISTER

M

NA2

TORRES F NA2

newhires

Concatenating SAS Data Sets: Execution

When SAS reaches end-of-file on the last data set,

DATA step execution ends.



Two SAS data sets, fa1 and fa2, contain data for newly

hired flight attendants. Concatenate the data sets into anew data set named newfa.

Name Gender JobCode

KENT F FA1PATEL M FA1JONES F FA1

Name JCode Gender

LOPEZ FA2 FGRANT FA2 F

fa1 fa2

Business Task



Name Gender JobCode JCode

KENT F FA1PATEL M FA1JONES F FA1LOPEZ F FA2GRANT F FA2

data newfa;set fa1 fa2;

run;

Concatenating SAS Data Sets: Execution

Name Gender JobCodeKENT F FA1PATEL M FA1JONES F FA1

Name JCode GenderLOPEZ FA2 FGRANT FA2 F

newfa

...

fa1 fa2



You can use a RENAME= data set option to change the

name of a variable.

General form of the RENAME= data set option:

SAS-data-set (RENAME=(old-name-1=new-name-1

old-name-2=new-name-2 .

.

.

old-name-n=new-name-n ))

The RENAME= Data Set Option



Name Gender JobCode Name Gender JobCode

KENT F FA1PATEL M FA1

JONES F FA1

LOPEZ F FA2

GRANT F FA2

data newfa;set fa1 fa2(rename=(JCode=JobCode));run;

Name Gender JobCodeKENT F FA1PATEL M FA1JONES F FA1

Name JCode GenderLOPEZ FA2 FGRANT FA2 F

newfa

The RENAME= Data Set Option

...

fa1 fa2



Use the SET statement with a BY statement in a DATA

step to interleave SAS data sets.

General form of a DATA step interleave:

DATA SAS-data-set ;

SET SAS-data-set1 SAS-data-set2 . . .;BY BY-variable ;<other SAS statements >

RUN;

Interleaving SAS Data Sets



Interleaving SAS data sets simply concatenates SAS data

sets so the observations in the resulting data set are inorder.


data work.allemp;

set ia.miamiemp

ia.parisemp

ia.romeemp; by ID;

run;

work.allemp

ID Salary

109 36000171 54000

ID Salary083 87000

217 42000

ID Salary059 60000

154 88000

ia.miamiemp

ia.parisemp

ia.romeemp

ID Salary059 60000083 87000109 36000

154 88000171 54000217 42000

c08s1d4 ...



Name Gender JobCode

GRANT F FA2

JONES F FA1

KENT F FA1

LOPEZ F FA2

PATEL M FA1

data newfa;

set fa1 fa2(rename=(JCode=JobCode)); by Name;run;

newfa


Name Gender JobCodeJONES F FA1KENT F FA1PATEL F FA1

Name JCode GenderGRANT FA2 FLOPEZ FA2 F

...

fa1 fa2



Merging SAS Data Sets



Prepare data for merging using the SORT procedureand data set options.

Merge SAS data sets on a single common variable.

Objectives



Merging SAS Data SetsUse the MERGE statement in a DATA step to join

corresponding observations from two or more SAS datasets.

DATA SAS-data-set ;MERGE SAS-data-sets;

BY BY-variable(s);<other SAS statements>

RUN;



17

SAS data sets

data compare; merge costs sales goals taxes; by Month;

run;

sales goalscosts taxes

compare

goals taxescosts sales

Merging SAS Data SetsYou can read any number of SAS data sets with a single

MERGE statement.



18

International Airlines iscomparing monthly salesperformance to monthly salesgoals.

The sales and goals data arestored in separate SAS datasets.

Business Task



19

To calculate the difference between revenues and goals, the

performance and goals data sets must be merged.

Month Sales

1 2118223

2 1960034

Month Goal

1 2130000

2 1920000

ia.performance ia.goals

Match-merge the data sets by Month and compute the

difference between the variable

values for Sales and Goal.

Month Sales Goal Difference

1 2118223 2130000 -11777

2 1960034 1920000 40034

ia.compare

Business Task



20

Merge two data sets to acquire the names of the German

crew who are scheduled to fly next week.

ia.gercrew ia.gersched

To match-merge the data sets by EmpID, the data sets

must be ordered by EmpID.

EmpID FlightNum

E04064 5105

E00632 5250E01996 5501

EmpID LastName

E00632 STRAUSS

E01483 SCHELL-HAUNGSE01996 WELLHAEUSSER

E04064 WASCHK

Business Task



21

data work.nextweek;

merge ia.gercrew work.gersched; by EmpID;

run;

EmpID FlightNum

E00632 5250

E01996 5501

E04064 5105

EmpID LastName

E00632 STRAUSS

E01483 SCHELL-HAUNGS

E01996 WELLHAEUSSER

E04064 WASCHK

work.

gersched

ia.

gercrew

EmpID LastName FlightNum

E00632 STRAUSS 5250


E01996 WELLHAEUSSER 5501

E04064 WASCHK 5105

work.nextweek

...

Merging SAS Data: Execution



22

Exclude from the data set crew members who are not

scheduled to fly next week.

ia.gercrew work.gersched EmpID LastName

E00632 STRAUSS


E01996 WELLHAEUSSER

E04064 WASCHK

EmpID FlightNum

E00632 5250

E01996 5501

E04064 5105

Eliminating Nonmatches



23

General form of the IN= data set option:

Use the IN= data set option to determine which data

set(s) contributed to the current observation.

Variable is a temporary numeric variable that hastwo possible values:

SAS-data-set (IN=variable )

0 indicates that the data set did not contribute to thecurrent observation.

1 indicates that the data set did contribute to the currentobservation.

The IN= Data Set Option



24

data work.combine;

merge ia.gercrew(in=InCrew)

work.gersched(in=InSched); by EmpID;

run;

EmpID LastName

E00632 STRAUSS

E01483 SCHELL-HAUNGSE01996 WELLHAEUSSER

E04064 WASCHK

PDV

EmpID FlightNum

E00632 5250

E01996 5501E04064 5105

work.gersched

ia.gercrew

EmpID LastName FlightNum InCrew InSched

E00632 STRAUSS 5250 1 1

DD

...

The IN= Data Set Option



25

Other MergesIn addition to one-to-one merges, the DATA step merge

works with many other kinds of data combinations:one-to-many unique BY values are in one

data set and duplicatematching BY values are in the

other data set.

many-to-many duplicate matching BY valuesare in both data sets.



26

work.two

X Z1 A1

1 A2

2 B1

3 C13 C2

work.one

X Y Z

1 A A1

1 A A2

2 B B1

3 C C1

3 C C2

work.threedata work.three;

merge work.one work.two;

by X;run;

...

X Y1 A

2 B

3 C

One-to-Many Merging



27

work.three

X Y

1 A1

1 A2

2 B1

2 B2

X Z

1 AA1

1 AA2

1 AA3

2 BB1

2 BB2

data work.three; merge work.one work.two;

by X;run;

X Y Z

1 A1 AA11 A2 AA2

1 A2 AA3

2 B1 BB1

2 B2 BB2

...

Many-to-Many Mergingwork.twowork.one



28

Exercises

1. Concatenating SAS Data Sets2. Merging SAS Data Sets

3. Identifying Data Set Contributors

lec5 student

Documents