Suppose you have a
SAS data set, MyData.Birthday, that contains employee ID numbers, last names, and birthdays. (See
Example Data for a description of MyData.Birthday.) You want to update this data set with data
that is described by Vlib.EmpBdat, a view descriptor that is based on the IMS EmplInf2
database. To perform this update, enter the following SAS statements:
libname vlib 'sas-data-library';
libname mydata 'sas-data-library';
options nodate;
/*---------------------------------------------*/
/* Update the BIRTHDAY SAS data set */
/* with data from IMS */
/* EmplInf2 database */
/*---------------------------------------------*/
options linesize=80;
proc sort data=mydata.birthday;
by employee_id;
run;
proc print data=mydata.birthday;
title2 'Sorted SAS Data Set MYDATA.BIRTHDAY';
run;
proc print data=vlib.empbday;
title2 'Data Described by VLIB.EMPBDAY';
run;
data mydata.newbday;
update mydata.birthday vlib.empbday;
by employee_id;
run;
proc print data=mydata.newbday;
title2 'SAS Data Set MYDATA.NEWBDAY';
run;
The EmplInf2 database is a Hidam database whose
root segment is sequenced by the
key field EMPID, so when the UPDATE statement references the view descriptor Vlib.EmpBdat,
the data is presented to SAS for updating in sorted order by EMPLOYEE_ID.
However, the SAS data set MyData.Birthday must be sorted before the update because
the UPDATE statement expects
both the original file and the transaction file to be sorted by the same BY
variable.
The following three
outputs show the results of the print procedures.
Data Set to Be Updated, MyData.Birthday, in EMPID Order
The SAS System
Sorted SAS Data Set MYDATA.BIRTHDAY
employee_
OBS id last_name birthday
1 1005 Knapp 06OCT38
2 1024 Mueller 17JUN53
3 1078 Gibson 23APR36
4 1247 Garcia 04APR54
IMS Data That Is Described by the View Descriptor Vlib.EmpBdat
The SAS System
Data Described by VLIB.EMPBDAY
EMPLOYEE_ PHONE_
OBS ID LAST_NAME FIRST_NAME BIRTHDAY EXTENSION
1 1001 Waterhouse Clifton P. 01JAN48 X5109
2 1002 Bowman Hugh E. 14JUL31 X5901
3 1003 Salazar Yolanda 12DEC40 X5169
4 1004 Knight Althea 09APR50 X5218
5 1005 Knapp Patrice R. 04OCT37 X5012
6 1006 Garrett Olan M. 23JAN35 X5208
7 1007 Brown Virgina P. 24MAY46 X5258
8 1008 Hernandez Jesse L. 26MAR33 X5448
9 1009 Jones Michael Y. 21MAY31 X5713
10 1010 Smith Janet F. 07AUG47 X5621
11 1011 Van Hotten Gwendolyn 13SEP42 X5311
12 1012 Quintero Pedro 21FEB48 X5348
13 1015 Scholl Madison A. 19MAR45 X5419
14 1017 Waggonner Merrilee D. 27APR36 X5914
15 1020 Rudd Fred .
16 1024 Mueller Patsy 17JUN52 X5822
17 1031 Chan Tai 04JUL46 X5331
18 1049 Fernandez Sophia 11SEP44 X5847
19 1050 Ameer David 10OCT51 X5495
20 1062 Littlejohn Fannie 17MAY54 X5653
21 1067 Cahill Jacob 25DEC40 X5042
22 1071 Canady Frank A. 19NOV41 X5406
23 1074 Millsap Joel B. 12JUN36 X5224
24 1077 Gibson Teddy B. 23APR46 X5703
25 1078 Gibson George J. 23APR46 X5703
26 1083 Savage William D. 20JAN53 X5505
27 1086 Schmidt Penny 19FEB27 X5822
28 1092 Polanski Ivan L. 11OCT47 X5621
29 1101 Nathaniel Darryl 21MAR44 X5544
30 1105 Faulkner Carrie Ann 17AUG51 X5417
31 1112 Jones Rita M. 24DEC48 X5271
32 1119 Goodson Alan F. 21JUN50 X5512
33 1120 Reid David G. 15AUG45 X5369
34 1123 Freeman Leopold 09FEB35 X5604
35 1133 Williamson Janice L. 19MAY52 X5802
36 1139 Seaton Gary 03OCT56 X5545
37 1145 Juarez Armando 28MAY47 X5987
38 1156 Reed Kenneth D. 05JAN55 X5307
39 1161 Richardson Travis Z. 30NOV37 X5325
40 1213 Johnson Bradford 15APR54 X5446
41 1217 Rodriguez Romualdo R. 09FEB29 X5874
42 1219 Kaatz Freddie 21JUN57 X5387
43 1234 Shropshire Leland G. 04SEP49 X5616
44 1238 Throckmort Stewart Q. 04AUG31 X5391
45 1247 Garcia Francisco 05MAY55 X5348
46 1261 Collins Lillian 01MAY51 X5616
47 1265 Slye Leonard R. 18DEC60 X5123
48 1266 Redfox Richard B. 04APR44 X5386
49 1272 Smith Garland P. 05APR54 X5415
50 1313 Smith Jerry Lee 13SEP42 X5169
51 1327 Brooks Ruben R. 25FEB52 X5347
52 1900 Smith John .
Data in the New Data Set MyData.NewBday
The SAS System
SAS Data Set MYDATA.NEWBDAY
employee_ PHONE_
OBS id last_name birthday FIRST_NAME EXTENSION
1 1001 Waterhouse 01JAN48 Clifton P. X5109
2 1002 Bowman 14JUL31 Hugh E. X5901
3 1003 Salazar 12DEC40 Yolanda X5169
4 1004 Knight 09APR50 Althea X5218
5 1005 Knapp 04OCT37 Patrice R. X5012
6 1006 Garrett 23JAN35 Olan M. X5208
7 1007 Brown 24MAY46 Virgina P. X5258
8 1008 Hernandez 26MAR33 Jesse L. X5448
9 1009 Jones 21MAY31 Michael Y. X5713
10 1010 Smith 07AUG47 Janet F. X5621
11 1011 Van Hotten 13SEP42 Gwendolyn X5311
12 1012 Quintero 21FEB48 Pedro X5348
13 1015 Scholl 19MAR45 Madison A. X5419
14 1017 Waggonner 27APR36 Merrilee D. X5914
15 1020 Rudd . Fred
16 1024 Mueller 17JUN52 Patsy X5822
17 1031 Chan 04JUL46 Tai X5331
18 1049 Fernandez 11SEP44 Sophia X5847
19 1050 Ameer 10OCT51 David X5495
20 1062 Littlejohn 17MAY54 Fannie X5653
21 1067 Cahill 25DEC40 Jacob X5042
22 1071 Canady 19NOV41 Frank A. X5406
23 1074 Millsap 12JUN36 Joel B. X5224
24 1077 Gibson 23APR46 Teddy B. X5703
25 1078 Gibson 23APR46 George J. X5703
26 1083 Savage 20JAN53 William D. X5505
27 1086 Schmidt 19FEB27 Penny X5822
28 1092 Polanski 11OCT47 Ivan L. X5621
29 1101 Nathaniel 21MAR44 Darryl X5544
30 1105 Faulkner 17AUG51 Carrie Ann X5417
31 1112 Jones 24DEC48 Rita M. X5271
32 1119 Goodson 21JUN50 Alan F. X5512
33 1120 Reid 15AUG45 David G. X5369
34 1123 Freeman 09FEB35 Leopold X5604
35 1133 Williamson 19MAY52 Janice L. X5802
36 1139 Seaton 03OCT56 Gary X5545
37 1145 Juarez 28MAY47 Armando X5987
38 1156 Reed 05JAN55 Kenneth D. X5307
39 1161 Richardson 30NOV37 Travis Z. X5325
40 1213 Johnson 15APR54 Bradford X5446
41 1217 Rodriguez 09FEB29 Romualdo R. X5874
42 1219 Kaatz 21JUN57 Freddie X5387
43 1234 Shropshire 04SEP49 Leland G. X5616
44 1238 Throckmort 04AUG31 Stewart Q. X5391
45 1247 Garcia 05MAY55 Francisco X5348
46 1261 Collins 01MAY51 Lillian X5616
47 1265 Slye 18DEC60 Leonard R. X5123
48 1266 Redfox 04APR44 Richard B. X5386
49 1272 Smith 05APR54 Garland P. X5415
50 1313 Smith 13SEP42 Jerry Lee X5169
51 1327 Brooks 25FEB52 Ruben R. X5347
52 1900 Smith . John