34594 - Modifying and Combining SAS Data Sets

Sample 34594: Modifying and Combining SAS Data Sets

These examples appear in Chapter 6 of The Little SAS Book: A Primer, Fourth Edition.

Before you run the examples, open the zip file under the Downloads tab. Using the list below, create the folders and copy the data files so that the data are available during your SAS session.

Create the folder c:\MyRawData and copy these files to it:

            
   Address.dat          IceCreamSales.dat         Shakespeare.dat      
   Admit.dat            IceCreamSales2.dat        Shoe.dat             
   AllScores.dat        Lengths.dat               Shoesales.dat        
   Artists.dat          Library.dat               South.dat             
   Baseball.dat         LibraryBooks.dat          Speed.dat              
   Basketball.dat       Mag.dat                   Survey.dat             
   Boats2.dat           Mens5000.dat              Temperature.dat      
   Boats.dat            Mountains.dat             Temps.dat             
   Books.dat            Movies.dat                ToadJump.dat         
   Bus.dat              NatPark.Dat               Tomatoes.dat          
   Candy.dat            NewAdmit.dat              Traffic.dat          
   Cars.dat             North.dat                 Train.dat             
   Choc.dat             Olympic1500.dat           Transpos.dat         
   Chocolate.dat        OnionRing.dat             TropicalSales.dat        
   Chocsales.dat        Onions.dat                UsedCars.dat         
   Coffee.dat           OrdersQ3.dat              Walk.dat             
   Criterium.dat        Parks.dat                 WBRK.dat             
   CustAddress.dat      Picbooks.dat              Zoo.dat                
   Disc.dat             Precipitation.dat                                  
   Exercise.dat         President.dat             Bands.csv             
   Flowers.dat          Pumpkin.dat               Bands2.csv             
   Games.dat            Records.dat               Women.csv            
   Garden.dat           Scores.dat                                     
   Home.dat             SeaLife.dat

Create the folder c:\MyFiles and copy this file to it: Baseball.xls

Create the folder c:\MyWebLogs and copy this file to it: dogweblogs.txt

Create the folder c:\MyExcelFiles and copy these files to it: Baseball.xls, OnionRing.xls

Create the folder c:\MyData to store files when you run examples

Create the folder c:\MyHTML to store files when you run examples

Create the folder c:\MyHTMLFiles to store files when you run examples

Create the folder c:\MyPDFFiles to store files when you run examples

Create the folder c:\MyRTFFiles to store files when you run examples

Create the folder c:\MySASLib and copy this file to it: TropicalSales.dat

Section 6.1
First Program

* Create permanent SAS data set trains; 
DATA 'c:\MySASLib\trains';    
   INFILE 'c:\MyRawData\Train.dat';    
   INPUT Time TIME5. Cars People; 
RUN;

Second Program

* Read the SAS data set trains with a SET statement;
DATA averagetrain;
   SET 'c:\MySASLib\trains';
   PeoplePerCar = People / Cars;
RUN;
PROC PRINT DATA = averagetrain;
   TITLE 'Average Number of People per Train Car';
   FORMAT Time TIME5.;
RUN;

Section 6.2

DATA southentrance;
   INFILE 'c:\MyRawData\South.dat';
   INPUT Entrance $ PassNumber PartySize Age;
PROC PRINT DATA = southentrance;
   TITLE 'South Entrance Data';
RUN;
DATA northentrance;
   INFILE 'c:\MyRawData\North.dat';
   INPUT Entrance $ PassNumber PartySize Age Lot;
PROC PRINT DATA = northentrance;
   TITLE 'North Entrance Data';
RUN; 

* Create a data set, both, combining northentrance and southentrance;
* Create a variable, AmountPaid, based on value of variable Age;
DATA both;
   SET southentrance northentrance;
   IF Age = . THEN AmountPaid = .;
      ELSE IF Age < 3  THEN AmountPaid = 0;
      ELSE IF Age < 65 THEN AmountPaid = 35;
      ELSE AmountPaid = 27;
PROC PRINT DATA = both;
   TITLE 'Both Entrances';
RUN;

Section 6.3

 
DATA southentrance;
   INFILE 'c:\MyRawData\South.dat';
   INPUT Entrance $ PassNumber PartySize Age;
PROC PRINT DATA = southentrance;
   TITLE 'South Entrance Data';
RUN;
DATA northentrance;
   INFILE 'c:\MyRawData\North.dat';
   INPUT Entrance $ PassNumber PartySize Age Lot;
PROC SORT DATA = northentrance;
   BY PassNumber;
PROC PRINT DATA = northentrance;
   TITLE 'North Entrance Data';
RUN;
* Interleave observations by PassNumber;
DATA interleave;
   SET northentrance southentrance;
   BY PassNumber;
PROC PRINT DATA = interleave;
   TITLE 'Both Entrances, By Pass Number';
RUN;

Section 6.4

DATA descriptions;     
   INFILE 'c:\MyRawData\chocolate.dat' TRUNCOVER;    
   INPUT CodeNum $ 1-4 Name $ 6-14 Description $ 15-60; 
RUN;
DATA sales;    
   INFILE 'c:\MyRawData\chocsales.dat';    
   INPUT CodeNum $ 1-4 PiecesSold 6-7; 
PROC SORT DATA = sales;
   BY CodeNum;
RUN;
* Merge data sets by CodeNum;
DATA chocolates;    
   MERGE sales descriptions;    
   BY CodeNum;
PROC PRINT DATA = chocolates;   
   TITLE "Today's Chocolate Sales"; 
RUN;

Section 6.5

DATA regular;
   INFILE 'c:\MyRawData\Shoe.dat';
   INPUT Style $ 1-15 ExerciseType $ RegularPrice;
PROC SORT DATA = regular;
   BY ExerciseType;
RUN;
DATA discount;
   INFILE 'c:\MyRawData\Disc.dat';
   INPUT ExerciseType $ Adjustment;
RUN;
* Perform many-to-one match merge;
DATA prices;
   MERGE regular discount;
   BY ExerciseType;
   NewPrice = ROUND(RegularPrice - (RegularPrice * Adjustment), .01);
PROC PRINT DATA = prices;
   TITLE 'Price List for May';
RUN;

Section 6.6

DATA shoes;
   INFILE 'c:\MyRawData\Shoesales.dat';
   INPUT Style $ 1-15 ExerciseType $ Sales;
PROC SORT DATA = shoes;
   BY ExerciseType;
RUN;
* Summarize sales by ExerciseType and print;
PROC MEANS NOPRINT DATA = shoes;
   VAR Sales;
   BY ExerciseType;
   OUTPUT OUT = summarydata SUM(Sales) = Total;
PROC PRINT DATA = summarydata;
   TITLE 'Summary Data Set';
RUN;
* Merge totals with the original data set;
DATA shoesummary;
   MERGE shoes summarydata;
   BY ExerciseType;
   Percent = Sales / Total * 100;
PROC PRINT DATA = shoesummary;
   BY ExerciseType;
   ID ExerciseType;
   VAR Style Sales Total Percent;
   TITLE 'Sales Share by Type of Exercise';
RUN;

Section 6.7

DATA shoes;
   INFILE 'c:\MyRawData\Shoesales.dat';
   INPUT Style $ 1-15 ExerciseType $ Sales;
RUN;
* Output grand total of sales to a data set and print;
PROC MEANS NOPRINT DATA = shoes;
   VAR Sales;
   OUTPUT OUT = summarydata SUM(Sales) = GrandTotal;
PROC PRINT DATA = summarydata;
   TITLE 'Summary Data Set';
RUN;
* Combine the grand total with the original data;
DATA shoesummary;
   IF _N_ = 1 THEN SET summarydata;
   SET shoes;
   Percent = Sales / GrandTotal * 100;
PROC PRINT DATA = shoesummary;
   VAR Style ExerciseType Sales GrandTotal Percent;
   TITLE 'Overall Sales Share';
RUN;

Section 6.8
First Program

	
LIBNAME perm 'c:\MySASLib';
DATA perm.patientmaster;
   INFILE 'c:\MyRawData\Admit.dat';
   INPUT Account LastName $ 8-16 Address $ 17-34
      BirthDate MMDDYY10. Sex $ InsCode $ 48-50 @52 LastUpdate MMDDYY10.;
RUN;

Second Program

LIBNAME perm 'c:\MySASLib';
DATA transactions;
   INFILE 'c:\MyRawData\NewAdmit.dat';
   INPUT Account LastName $ 8-16 Address $ 17-34 BirthDate MMDDYY10. 
      Sex $ InsCode $ 48-50 @52 LastUpdate MMDDYY10.;
PROC SORT DATA = transactions;
   BY Account;
RUN;
* Update patient data with transactions;
DATA perm.patientmaster;
   UPDATE perm.patientmaster transactions;
   BY Account;
PROC PRINT DATA = perm.patientmaster;
   FORMAT BirthDate LastUpdate MMDDYY10.;
   TITLE 'Admissions Data';
RUN;

Section 6.9

DATA morning afternoon;
   INFILE 'c:\MyRawData\Zoo.dat';
   INPUT Animal $ 1-9 Class $ 11-18 Enclosure $ FeedTime $;
   IF FeedTime = 'am' THEN OUTPUT morning;
      ELSE IF FeedTime = 'pm' THEN OUTPUT afternoon;
      ELSE IF FeedTime = 'both' THEN OUTPUT;
RUN;
PROC PRINT DATA = morning;
   TITLE 'Animals with Morning Feedings';
PROC PRINT DATA = afternoon;
   TITLE 'Animals with Afternoon Feedings';
RUN;

Section 6.10
First Program

	
* Create data for variables x and y;
DATA generate;
   DO x = 1 TO 6;
      y = x ** 2;
      OUTPUT;
   END;
PROC PRINT DATA = generate;
   TITLE 'Generated Data';
RUN;

Program 2

* Create three observations for each data line read
*   using three OUTPUT statements;
DATA theaters;
   INFILE 'c:\MyRawData\Movies.dat';
   INPUT Month $ Location $ Tickets @;
   OUTPUT;
   INPUT Location $ Tickets @;
   OUTPUT;
   INPUT Location $ Tickets;
   OUTPUT;
RUN;
PROC PRINT DATA = theaters;
   TITLE 'Ticket Sales';
RUN;

Section 6.12

DATA customer;
   INFILE 'c:\MyRawData\CustAddress.dat' TRUNCOVER;
   INPUT CustomerNumber Name $ 5-21 Address $ 23-42;
DATA orders;
   INFILE 'c:\MyRawData\OrdersQ3.dat';
   INPUT CustomerNumber Total;
PROC SORT DATA = orders;
   BY CustomerNumber;
RUN;
* Combine the data sets using the IN= option;
DATA noorders;
   MERGE customer orders (IN = Recent);
   BY CustomerNumber;
   IF Recent = 0;
PROC PRINT DATA = noorders;
   TITLE 'Customers with No Orders in the Third Quarter';
RUN;

Section 6.13

*Input the data and create two subsets;
DATA tallpeaks (WHERE = (Height > 6000))
     american (WHERE = (Continent CONTAINS ('America')));
   INFILE 'c:\MyRawData\Mountains.dat';
   INPUT Name $1-14 Continent $15-28 Height;
RUN;
PROC PRINT DATA = tallpeaks;
   TITLE 'Members of the Seven Summits above 6,000 Meters';
PROC PRINT DATA = american; 
   TITLE 'Members of the Seven Summits in the Americas';
RUN;

Section 6.14

DATA baseball;
   INFILE 'c:\MyRawData\Transpos.dat';
   INPUT Team $ Player Type $ Entry;
PROC SORT DATA = baseball;
   BY Team Player;
PROC PRINT DATA = baseball;
   TITLE 'Baseball Data After Sorting and Before Transposing';
RUN;
* Transpose data so salary and batavg are variables;
PROC TRANSPOSE DATA = baseball OUT = flipped;
   BY Team Player;
   ID Type;
   VAR Entry;
PROC PRINT DATA = flipped;
   TITLE 'Baseball Data After Transposing';
RUN;

Section 6.15

DATA walkers;   
   INFILE 'c:\MyRawData\Walk.dat';   
   INPUT Entry AgeGroup $ Time @@; 
PROC SORT DATA = walkers;    
   BY Time; 
* Create a new variable, Place; 
DATA ordered;    
   SET walkers;
   Place = _N_;
PROC PRINT DATA = ordered;
  TITLE 'Results of Walk';

PROC SORT DATA = ordered;
   BY AgeGroup Time;
* Keep the first observation in each age group;
DATA winners;
   SET ordered;
   BY AgeGroup;
   IF FIRST.AgeGroup = 1;
PROC PRINT DATA = winners;
   TITLE 'Winners in Each Age Group';
RUN;

The sample is authored by Lora D. Delwiche and Susan J. Slaughter.
Their book The Little SAS Book: A Primer, Fourth Edition is available for sale in our online bookstore.

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

Section 6.1, Output

                             Average Number of People per Train Car

                                                               People
                            Obs     Time    Cars    People     PerCar

                             1     10:10      6       21      3.50000
                             2     12:15     10       56      5.60000
                             3     15:30     10       25      2.50000
                             4     11:30      8       34      4.25000
                             5     13:15      8       12      1.50000
                             6     10:45      6       13      2.16667
                             7     20:30      6       32      5.33333
                             8     23:15      6       12      2.00000

Section 6.2, Output

                                       South Entrance Data

                                                Pass     Party
                            Obs    Entrance    Number     Size    Age

                             1        S          43        3       27
                             2        S          44        3       24
                             3        S          45        3        2

                                       North Entrance Data

                                            Pass     Party
                        Obs    Entrance    Number     Size    Age    Lot

                         1        N          21        5       41     1
                         2        N          87        4       33     3
                         3        N          65        2       67     1
                         4        N          66        2        7     1

                                         Both Entrances

                                       Pass     Party                  Amount
                   Obs    Entrance    Number     Size    Age    Lot     Paid

                    1        S          43        3       27     .       35
                    2        S          44        3       24     .       35
                    3        S          45        3        2     .        0
                    4        N          21        5       41     1       35
                    5        N          87        4       33     3       35
                    6        N          65        2       67     1       27
                    7        N          66        2        7     1       35

Section 6.3, Output

                                       South Entrance Data

                                                Pass     Party
                            Obs    Entrance    Number     Size    Age

                             1        S          43        3       27
                             2        S          44        3       24
                             3        S          45        3        2

                                       North Entrance Data

                                            Pass     Party
                        Obs    Entrance    Number     Size    Age    Lot

                         1        N          21        5       41     1
                         2        N          65        2       67     1
                         3        N          66        2        7     1
                         4        N          87        4       33     3

                                 Both Entrances, By Pass Number

                                            Pass     Party
                        Obs    Entrance    Number     Size    Age    Lot

                         1        N          21        5       41     1
                         2        S          43        3       27     .
                         3        S          44        3       24     .
                         4        S          45        3        2     .
                         5        N          65        2       67     1
                         6        N          66        2        7     1
                         7        N          87        4       33     3

Section 6.4, Output

                                     Today's Chocolate Sales

              Code    Pieces
       Obs    Num      Sold     Name                         Description

        1     A206      12      Mokka        Coffee buttercream in dark chocolate
        2     A536      21      Walnoot      Walnut halves in bed of dark chocolate
        3     B713      29      Frambozen    Raspberry marzipan covered in milk chocolate
        4     C865      15      Vanille      Vanilla-flavored rolled in ground hazelnuts
        5     K014       1      Kroon        Milk chocolate with a mint cream center
        6     K086       9      Koning       Hazelnut paste in dark chocolate
        7     M315       .      Pyramide     White with dark chocolate trimming
        8     S163      34      Orbais       Chocolate cream in dark chocolate

Section 6.5, Output

                                       Price List for May

                                        Exercise    Regular                    New
              Obs    Style                Type       Price     Adjustment     Price

               1     Zip Sneak          c-train       92.99       0.25        69.74
               2     Max Flight         running      142.99       0.30       100.09
               3     Zoom Airborne      running      112.99       0.30        79.09
               4     Zip Fit Leather    walking       83.99       0.20        67.19
               5     Light Step         walking       73.99       0.20        59.19
               6     Max Step Woven     walking       75.99       0.20        60.79

Section 6.6, First Output

                                        Summary Data Set

                                 Exercise
                          Obs      Type      _TYPE_    _FREQ_    Total

                           1     c-train        0         1       1190
                           2     running        0         2       6080
                           3     walking        0         3       5610

Section 6.6, Second Output

                                 Sales Share by Type of Exercise

                    Exercise
                      Type      Style              Sales    Total    Percent

                    c-train     Zip Sneak           1190     1190    100.000

                    running     Max Flight          1930     6080     31.743
                                Zoom Airborne       4150     6080     68.257

                    walking     Zip Fit Leather     2250     5610     40.107
                                Light Step          1130     5610     20.143
                                Max Step Woven      2230     5610     39.750

Section 6.7, Output

                                        Summary Data Set

                                                           Grand
                                Obs    _TYPE_    _FREQ_    Total

                                 1        0         6      12880

                                       Overall Sales Share

                                           Exercise             Grand
                 Obs    Style                Type      Sales    Total    Percent

                  1     Max Flight         running      1930    12880    14.9845
                  2     Zip Fit Leather    walking      2250    12880    17.4689
                  3     Zoom Airborne      running      4150    12880    32.2205
                  4     Light Step         walking      1130    12880     8.7733
                  5     Max Step Woven     walking      2230    12880    17.3137
                  6     Zip Sneak          c-train      1190    12880     9.2391

Section 6.8, Output

                                         Admissions Data

                                                                           Ins
   Obs    Account    LastName    Address               BirthDate    Sex    Code    LastUpdate

    1      235777    Harman      5656 Land Way        01/18/2000     f     MCD     06/15/2008
    2      620135    Smith       234 Aspen St.        12/21/1975     m     HLT     06/15/2008
    3      645722    Miyamoto    65 3rd Ave.          04/03/1936     f     MCR     05/30/1999
    4      645739    Jensvold    505 Glendale Ave.    06/15/1960     f     HLT     09/23/2006
    5      874329    Kazoyan     76-C La Vista        04/24/1954     m     MCD     06/15/2008

Section 6.9, Output

                                  Animals with Morning Feedings

                                                                     Feed
                        Obs    Animal        Class      Enclosure    Time

                         1     bears        Mammalia       E2        both
                         2     elephants    Mammalia       W3        am
                         3     kangaroos    Mammalia       N4        am
                         4     tigers       Mammalia       W9        both
                         5     zebras       Mammalia       W2        am

                                 Animals with Afternoon Feedings

                                                                     Feed
                        Obs    Animal       Class       Enclosure    Time

                         1     bears        Mammalia       E2        both
                         2     flamingos    Aves           W1        pm
                         3     frogs        Amphibia       S2        pm
                         4     lions        Mammalia       W6        pm
                         5     snakes       Reptilia       S1        pm
                         6     tigers       Mammalia       W9        both

Section 6.10, First Output

                                         Generated Data

                                         Obs    x     y

                                          1     1     1
                                          2     2     4
                                          3     3     9
                                          4     4    16
                                          5     5    25
                                          6     6    36

Section 6.10, Second Output

                                          Ticket Sales

                               Obs    Month    Location    Tickets

                                1      Jan     Varsity      56723
                                2      Jan     Downtown     69831
                                3      Jan     Super-6      70025
                                4      Feb     Varsity      62137
                                5      Feb     Downtown     43901
                                6      Feb     Super-6      81534
                                7      Mar     Varsity      49982
                                8      Mar     Downtown     55783
                                9      Mar     Super-6      69800

Section 6.12, Output

                          Customers with No Orders in the Third Quarter

                         Customer
                  Obs     Number           Name             Address      Total

                   1        103      Sports Outfitters    19 Cary Way      .

Section 6.13, Output

                         Members of the Seven Summits above 6,000 Meters

                           Obs      Name       Continent        Height

                            1     Everest      Asia              8848
                            2     McKinley     North America     6194
                            3     Aconcagua    South America     6962

                          Members of the Seven Summits in the Americas

                           Obs      Name         Continent      Height

                            1     McKinley     North America     6194
                            2     Aconcagua    South America     6962

Section 6.14, Output


                       Baseball Data After Sorting and Before Transposing

                         Obs     Team      Player     Type        Entry

                          1     Garlics      10      salary    43000.00
                          2     Garlics      10      batavg        0.28
                          3     Garlics      21      salary    51000.00
                          4     Garlics      21      batavg        0.27
                          5     Peaches       8      salary    38000.00
                          6     Peaches       8      batavg        0.25
                          7     Peaches      10      salary    47500.00
                          8     Peaches      10      batavg        0.30

                                 Baseball Data After Transposing

                     Obs     Team      Player    _NAME_    salary    batavg

                      1     Garlics      10      Entry      43000     0.281
                      2     Garlics      21      Entry      51000     0.265
                      3     Peaches       8      Entry      38000     0.252
                      4     Peaches      10      Entry      47500     0.301

Section 6.15, Output

                                         Results of Walk

                                              Age
                             Obs    Entry    Group     Time    Place

                               1       3     adult     19.0       1
                               2      21     adult     21.6       2
                               3      11     adult     21.9       3
                               4       6     adult     25.8       4
                               5      13     senior    29.0       5
                               6      54     youth     35.5       6
                               7      32     youth     38.6       7
                               8      19     youth     39.6       8
                               9      38     senior    40.3       9
                              10      41     adult     43.0      10
                              11      25     youth     47.3      11
                              12       8     senior    54.3      12


                                    Winners in Each Age Group

                                              Age
                             Obs    Entry    Group     Time    Place

                              1        3     adult     19.0      1
                              2       13     senior    29.0      5
                              3       54     youth     35.5      6

Date Modified:	2009-01-29 17:19:02
Date Created:	2009-01-26 11:02:59

Product Family	Product	Host	SAS Release
Product Family	Product	Host	Starting	Ending
SAS System	Base SAS	z/OS
		OpenVMS VAX
		Microsoft® Windows® for 64-Bit Itanium-based Systems
		Microsoft Windows Server 2003 Datacenter 64-bit Edition
		Microsoft Windows Server 2003 Enterprise 64-bit Edition
		Microsoft Windows XP 64-bit Edition
		Microsoft® Windows® for x64
		OS/2
		Microsoft Windows 95/98
		Microsoft Windows 2000 Advanced Server
		Microsoft Windows 2000 Datacenter Server
		Microsoft Windows 2000 Server
		Microsoft Windows 2000 Professional
		Microsoft Windows NT Workstation
		Microsoft Windows Server 2003 Datacenter Edition
		Microsoft Windows Server 2003 Enterprise Edition
		Microsoft Windows Server 2003 Standard Edition
		Microsoft Windows XP Professional
		Windows Millennium Edition (Me)
		Windows Vista
		64-bit Enabled AIX
		64-bit Enabled HP-UX
		64-bit Enabled Solaris
		ABI+ for Intel Architecture
		AIX
		HP-UX
		HP-UX IPF
		IRIX
		Linux
		Linux for x64
		Linux on Itanium
		OpenVMS Alpha
		OpenVMS on HP Integrity
		Solaris
		Solaris for x64
		Tru64 UNIX

E-mail This Page

Knowledge Base

Sample 34594: Modifying and Combining SAS Data Sets

Operating System and Release Information

E-mail:
Password