1 / 33

Modifying and Combining SAS Data Sets

Learn how to modify SAS data sets using the SET statement, subset data, stack sets, interleave data, and merge sets for analysis. Examples and syntax provided.

mparkey
Download Presentation

Modifying and Combining SAS Data Sets

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. EPIB 698E SAS Lecture 5 Modifying and combining SAS data sets Raul Cruz-Cano Fall 2013

  2. Modifying a data set with the SET statement The SET statement • The SET statement in the data step allows you to read a SAS data set so that you can add new variables, create a subset, or modify the data set • The SET statement brings a SAS data set, one observation at a time, in to a data step for processing • Syntax: Data new-data-set ; Set data set;

  3. Modifying a data set with the SET statement Data new; input x y ; cards; 1 2 3 4 ; run; Data new1; set new; z=x+y; run;

  4. Subsetting your data • You can subset you data using a IF statement in a data step • Example: Data new1; Set new; If gender =‘F’; Data new1; Set new; If gender ^=‘F’ then delete;

  5. Stacking data sets using the SET statement • With more than one data, the SET statement stacks the data sets one on top of the other • Syntax: DATA new-data-set; SET data-set-1 data-set-2 … data-set-n; • The Number of observations in the new data set will equal to the sum of the number of observations in the old data sets • The order of observations is determined by the order of the list of old data sets • If one of the data set has a variables not contained in the other data sets, then observations from the other data sets will have missing values for that variable

  6. Stacking data sets using the SET statement • Example: Here is data set contains information of visitors to a park. There are two entrances: south entrance and north entrance. The data file for the south entrance has an S for south, followed by the customers pass numbers, the size of their parties, and ages. The data file for the north entrance has an N for north, the same data as the south entrance, plus one more variable for parking lot. • /* North.dat */ • N 21 5 41 1 • N 87 4 33 3 • N 65 2 67 1 • N 66 2 7 1 /* South .dat */ S 43 3 27 S 44 3 24 S 45 3 2

  7. DATA southentrance; INPUT Entrance $ PassNumber PartySize Age; cards; S 43 3 27 S 44 3 24 S 45 3 2 ; RUN; DATA northentrance; INPUT Entrance $ PassNumber PartySize Age Lot; Cards; N 21 5 41 1 N 87 4 33 3 N 65 2 67 1 N 66 2 7 1 ; RUN; DATA both; SET southentrance northentrance; RUN;

  8. Interleaving data sets using the SET statement • If you have data sets that are already sorted by some important variable, then simply stacking the data sets may unsort the data sets. You can add a BY statement to keep the final data set in a sorted version. • Syntax: DATA new-data-set; SET data-set-1 data-set-2 … data-set-n; BY variable-list; The old data sets need to be sorted by the BY variables

  9. The Park Data • Example: The data file for the south entrance has an S for south, followed by the customers’ pass numbers, the size of their parties, and ages. The data file for the north entrance has an N for north, the same data as the south entrance, plus one more variable for parking lot. Suppose the data sets have been sorted by the customer’s pass numbers • /* North.dat */ • N 21 5 41 1 • N 65 2 67 1 • N 66 2 7 1 • N 87 4 33 3 /* South .dat */ S 43 3 27 S 44 3 24 S 45 3 2

  10. PROCsortDATA = southentrance; by PassNumber; run; PROCsortDATA = northentrance; by PassNumber; run; DATA interleave; SET northentrance southentrance; BY PassNumber; run;

  11. Combining data sets with one-to-one match merge • The MERGE statement match observations from one data set with observations from another • If the two data sets are in EXACTLY the same order, you don’t have to have any common variables between the two data sets • However, usually you want to have, for matching purpose, some common variables which can uniquely identify each observation • Syntax DATA new-data-set; Merge data-set-1 data-set-2; By variable-list;

  12. Combining data sets with one-to-one match merge Chocolate sales example: • A store keeps all the chocolate sales data each day which contains the code number of the products and the number of pieces sold that day • In a separate file, they keep the detailed information of the products • We need to merge the two data sets in order to print the day’s sales along with the descriptions of the products.

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

  14. DATA descriptions; INFILE'F:\chocolate.txt‘TRUNCOVER; INPUT CodeNum $ 1-4 Name $ 6-14 Description $ 15-60; run; DATA sales; INFILE‘F:\sales.txt'; INPUT CodeNum $ PiecesSold ; run; PROCSORTDATA = sales; BY CodeNum; run; DATA chocolates; MERGE sales descriptions; BY CodeNum; run; TRUNCOVER: overrides the default behavior of the INPUT statement when an input data record is shorter than the INPUT statement expects. By default, the INPUT statement automatically reads the next input data record. TRUNCOVER: enables you to read variable-length records when some records are shorter than the INPUT statement expects.

  15. TRUNCOVER • You need the TRUCOVER option when you are reading data using the column or formatted input and some data lines are shorter than others. • IF a variable’s field extends past the end of the data line, by default, SAS will go to the next line to start reading the variable’s value. • The TRUCOVER option tells SAS to read data for the variable until it reach the end of the data line, or the last column specified in the format or column range, WHICHEVER COMES FIRST.

  16. Combining data sets with one-to-many match • One-to-many match: matching one observation from one data set with more than one observation to another data set • The statement of one-to-many match is the same as one-to-one match DATA new-data-set; Merge data-set-1 data-set-2; By variable-list; • The data sets must be sorted first by the BY variables • If the two data sets have variables with the same names, besides the BY variables, the variables from the second data set will overwrite any variables with the same name in the first data set

  17. Example: Shoes data • The shoe store is putting all its shoes on sale. They have two data file, one contains information about each type of shoe, and one with discount information. We want to find out new price of the shoes Discount data c-train .25 running .30 walking .20 Shoe data: Max Flight running 142.99 Zip Fit Leather walking 83.99 Zoom Airborne running 112.99 Light Step walking 73.99 Max Step Woven walking 75.99 Zip Sneak c-train 92.99

  18. DATA regular; INFILE datalines dsd; length style $15; INPUT Style $ ExerciseType $ RegularPrice @@; datalines; Max Flight , running, 142.99, … ; RUN; PROCSORTDATA = regular; BY ExerciseType; RUN; DATA discount; INPUT ExerciseType $ Adjustment @@; cards; c-train .25 … ; RUN; DATA prices; MERGE regular discount; BY ExerciseType; NewPrice = ROUND(RegularPrice - (RegularPrice * Adjustment), .01); RUN;

  19. Updating a master data set with transactions • The update statement is used when you have a master data set that must be updated with some new information. • The basic form of the UPDATE statement is DATA master-data-set; UPDATE master-data-set transaction-data-set; BY variable-list; • With the UPDATE statement, the resulting master data set has just one observation for each unique value of the common variables • Missing values in the transaction data do not overwrite the existing values of the master data set

  20. Example: hospital data • A hospital maintains a master database with information about patients. Account Name Address DOB Sex Insur lastupdate 620135 Smith 234 Aspen St. 12-21-1975 m CBC 02-16-1998 645722 Miyamoto 65 3rd Ave. 04-03-1936 f MCR 05-30-1999 645739 Jensvold 505 Glendale Ave. 06-15-1960 f HLT 09-23-1993 874329 Kazoyan 76-C La Vista . . MCD 01-15-2003 • The hospital create a transaction record for every new patient and any returning patient whose status has changed Account Name Address DOB Sex Insur lastupdate 620135 . . . . HLT 06-15-2003 874329 . . 04-24-1954 m . 06-15-2003 235777 Harman 5656 Land Way 01-18-2000 f MCD 06-15-2003

  21. LIBNAME perm ‘E:\'; DATA perm.patientmaster; INFILE'F:\Admit.txt'; INPUT Account LastName $ 8-16 Address $ 17-34 BirthDate MMDDYY10. Sex $ InsCode $ 48-50 @52 LastUpdate MMDDYY10.; run; DATA transactions; INFILE‘E:\NewAdmit.txt'; INPUT Account LastName $ 8-16 Address $ 17-34 BirthDate MMDDYY10. Sex $ InsCode $ 48-50 @52 LastUpdate MMDDYY10.; run; PROCSORTDATA = transactions; BY Account; run; DATA perm.patientmaster; UPDATE perm.patientmaster transactions; BY Account; run;

  22. SAS data set options • SAS has three basic types of options: system options, statement options, and data set options • System options are those that stay in effect for the duration of your job or session. These options affect how SAS operates, and usually issued when you invoke SAS or via Options statement. Eg: Options center nodate • Statement options appear in individual statement and influence how SAS runs that particular DATA or PROC step. Eg: Proc means data =new noprint; • Data set options affect only how SAS reads or writes an individual data set. You can use data set options in DATA step (in DATA, SET, MERGE, or UPDATE statements) or in PROC step ( in conjunct with a DATA=statement option). Eg: DATA New; set old (keep= x1 x2);

  23. SAS data set options • To use a data set option, you simply put it between parentheses directly following the data set name. Here are some commonly used data set options:

  24. Data new; input x y @@; datalines; 1 2 3 4 5 6 7 8 ; run; data new1; set new (keep=x firstobs=2 obs=3 rename= (x=z) ); run;

  25. Tracking and selecting observations with the IN=option • The IN=new-var-name option creates a new temporary variable. The variable exist only for the duration of the current DATA step and are not added to the data set being created • SAS gives IN=new-var-name a value of 0 if that data set did not contribute to the current observation and a value of 1 if it did • You can use the IN=new-var-name to track, select, or delete observations based on the data set of origin • The IN=new-var-name option is most often used with MERGE statement

  26. data new1; input ID $ gender $ ; cards; 1 F 2 M 3 F ; run; data new2 ; input ID $ age; cards; 1 15 3 20 4 35 ; run; data new3; merge new1 (In=indicator1) new2 (In=indicator2); by ID; if indicator1=1 and indicator2=1; run;

  27. Writing multiple datasets using the OUTPUT statement • You can create more than one data set in a single DATA step by putting multiple data set names after the DATA: DATA dataset1 dataset2 dataset3; • IF you want the data sets to be different, you need to combine the above data statement with an OUTPUT statement. The basic form of an output statement is: OUTPUT data-set-name; • IF you leave out the data-set-name after the OUTPUT, then the observation will be written to all data sets named in the DATA statement

  28. Example: A local zoo maintains a data base about the feeding of animals. For each groups of animals, the data include the scientific class, the enclosure those animals live in and whether they get fed in the morning, afternoon or both. Here is the data: bears Mammalia E2 both elephants Mammalia W3 am flamingos Aves W1 pm frogs Amphibia S2 pm kangaroos Mammalia N4 am lions Mammalia W6 pm snakes Reptilia S1 pm tigers Mammalia W9 both zebras Mammalia W2 am We want to create two list, one for morining feedings and one for afternoon feedings

  29. DATA morning afternoon; INFILE ‘E:\Zoo.txt'; INPUT Animal $ 1-9 Class $ 11-18 Enclosure $ FeedTime $; IF FeedTime = 'am'THENOUTPUT morning; ELSEIF FeedTime ='pm'THENOUTPUT afternoon; ELSEIF FeedTime = 'both'THENOUTPUT; Run;

  30. Use SAS automatic variables • In addition to the variables you created in your SAS data set, SAS creates a few automatic variable. These variables are temporary and are not saved with your data. But they are available in the DATA step and you can use them just like you use any variables that your created yourself • _N_: indicates the number of times SAS has looped through the DATA step • _ERROR_: has a value of 1 if there is a data error for that observation and 0 if there isn’t • First.variable and Last.variable: available when you are using a BY statement in a DATA step. The First.variable =1 (Last.variable =1 ) is when an observation with the first (last) occurrence of a new value for that variable and 0 for the other observations.

  31. 54 youth 35.5 21 adult 21.6 6 adult 25.8 13 senior 29.0 38 senior 40.3 19 youth 39.6 3 adult 19.0 25 youth 47.3 11 adult 21.9 8 senior 54.3 41 adult 43.0 32 youth 38.6 • Example: here is a data set contains information of walking race. It contains subject ID, age group and finishing time

  32. DATA walkers; INPUT EntryID AgeGroup $ Time @@; cards; 54 youth 35.5 21 adult 21.6 6 adult 25.8 13 senior 29.0 38 senior 40.3 19 youth 39.6 3 adult 19.0 25 youth 47.3 11 adult 21.9 8 senior 54.3 41 adult 43.0 32 youth 38.6 ; run; PROCSORTDATA = walkers; BY Time; run; DATA ordered; SET walkers; Place = _N_; Run;

  33. Hw#4 • Due on Tuesday (October 15, 2013)

More Related