5. TRANSFORMING SAS DATA SETS - Rho

1 5-15. TRANSFORMING SAS data new SAS data and TRANSFORMING variables Reading Assignment:Selected SAS Documentation for Bios111 Part 3: TRANSFORMING SAS data SetsREVISED FALL 20005-2 Creating New SAS data SetsIt will often be desirable to modify an existing SAS data set in some way--selecting only a subset of the originalobservations, TRANSFORMING variables, creating new variables, etc. These kinds of modifications areaccomplished within a data A data Stepv Reads one or more input data sets (SAS and/or non-SAS)v Performs processing (transformations, selections, etc.), if specifiedv Creates one or more output data sets (SAS or non-SAS)v In this chapter we will only discuss reading a single input SAS data set and creating a single output SASdata set. The other possibilities will be covered in subsequent All of the modification statements we will discuss can be used with any combination of input and of A data StepA data step that creates a single output SAS data set by modifying a single input SAS data set has a five part data statement to start the step and name the output data SET statement to read an observation from the input data statements to perform the processing required for this OUTPUT statement to write the observation to the output data set5.

2 A RETURN statement to end processing of this observation and return to the top of the step5-3 The data StatementThe data statement has two functions:v It defines the start of a data stepv It names the SAS data sets to be createdSyntax: data ; WhereDatasetis the name of the SAS data set to be createdLibref is the libref for a SAS data library in which the data set will be storedThe SET Statementv The SET statement reads an observation from an input SAS data set each time it is All variables in the input SAS data set are automatically passed to the new SAS data set (unless otherwisedirected with programming All observations in the input SAS data set are automatically passed to the new SAS data set (unlessotherwise directed with programming New variables may be added with assignment Note that reading a data set does not modify it in any :SET ; WhereDatasetis the name of an existing SAS data set to be readLibref is the libref for a SAS data library in which the data set is5-4 The OUTPUT Statementv The OUTPUT statement controls when the values in the program data vector (PDV) are written to theoutput SAS datav The OUTPUT statement is optionalv When the OUTPUT statement appears in the data step, there is no automatic output at the end of a data stepv When the OUTPUT statement does not appears in the data step, SAS outputs the values of the PDV at theend of the data stepv When an OUTPUT statement is executed, SAS immediately outputs the current PDV values to a SAS datasetv Execution of the OUTPUT statement does not return control to the beginning of the data stepSyntax:OUTPUT; or OUTPUT SASdataset(s).))

3 The RETURN Statementv The RETURN statement is usually the last statement in the data step. It indicates that processing of thecurrent observation is finished. SAS then returns to the data statement at the beginning of the step andprocesses the next The RETURN statement is optional. If the RETURN statement is omitted, execution returns to the top ofthe data step when a RUN or a PROC statement is :RETURN;5-5 Processing of a data StepThe processing of every data step involves two distinct First, SAS compiles the statements within the step, creating a program to perform the processing requestedv Second, the program created is executed, processing the data and creating the new data setv An Example data Step: data ;SET ;OUTPUT;RETURN;RUN;5-6 The Compilation PhaseDuring the compilation phase, the data compiler:v The SET statement reads the descriptor portion of the existing SAS data setv Creates the descriptor part of the output data setv Creates the program data vector which will contain all of the variables found in the existing SAS data setplus any new variables created with assignment statementsv Creates a machine language program to perform the processingv Detects syntax errorsThe Execution PhaseDuring the execution phase.

4 V The SET statement is executed once for each observation in the existing SAS data setv Each time the SET statement is executed, it reads an observation from the existing SAS data set and writesthe current observation to the PDVv Any program statements in the data step are executed once for each observation in the input data setv The values in the PDV are written to the new SAS data set after the last executable statement in the data step or when an OUTPUT statement is executed5-7 Flowchart of Execution:Initialize PDV to Missing data ; END YesofInputNoRead Next Observation into PVD SET ;Modify data Values in PDVW rite Values From PDV to Output data Set OUTPUT; RETURN;First Output data Set and Go to Next Step5-8 SAS data SEX AGE HT WTCHAR CHAR NUM NUM NUM12 1588 CHRISTIANSEN M 37 71 195 HOSKING J M 31 70 160 HELMS R M 41 74 195 PIGGY M F.

5 48 .FROG K M 3 12 1 GONZO 14 25 45 Program data vectorNAME SEX AGE HT WT||| |SAS data SEX AGE HT WTCHAR CHAR NUM NUM NUM12 13885-9 Summary--Creating New SAS data SetsThe four statements just described ( data , SET, OUTPUT, RETURN) are used whenever we want to create a new SAS data set from an existing one. Other statements are added to the step in order to make the output data set a modified version of the input data set, rather than an exact this chapter, we only discuss creating SAS data sets from other, already existing SAS data a SAS data set from a non-SAS data set ( , ascii or Dbase file) is a more complex task,which will be covered in detail later in the a new data set does not delete or modify the input data set; it is still available for use insubsequent and TRANSFORMING VariablesIn many cases, the reason for creating a new SAS data set will be to create new variables that are somecombination of existing variables, or to transform an existing example, we might want to add a new variable to the class data set called RELWT (for relative weight)whose value for each observation is defined by the algebraic formula:RELWT=WT/HT.

6 That is, the person s weight divided by their example of TRANSFORMING an existing variable would be recoding the values of height from English units(inches) to metric units (centimeters). The formula in this case is:HT= ;that is, take each person s current value of weight, multiply it by and use that result to replace the kinds of operations are performed in a data step using assignment Assignment Statementv The assignment statement is used to create new variables or to transform existing Syntax: variable = expression; where variable is the name of a variable in (or to be added to) the data set expression is an arithmetic expression, as defined belowv Examples:RELWT = WT/HT;HT= *ht;5-11v Notes:v The assignment is one (of two) exceptions to the rule that every SAS statement begins with akeywordv If "variable" is the name of an already existing variable, the value of "expression" replaces theprevious value.

7 If "variable" is a new name, the assignment statement creates a new variable,which is added to the output data setExpressionsv An expression consists of one or more constants, variables, and functions, combined by A constant is a number ( , 1, - , .00/) or, a character string ( , JOHN , MALE , X#! );character constants must be enclosed in single quotes (apostrophes). (SAS also allows other,specialized types of constants; we will discuss some of them later in the course.)v A function is a program "built in" to SAS that performs some computation on character or numeric An operator is a mathematical, logical, or character operation or manipulation that combines, compares, ortransforms numeric or character Operators perform basic arithmetic calculations Symbol Action + addition - subtraction * multiplication / division ** exponentiation5-12 Comparison operators look at the relationship between two quantities Symbol Mnemonic Equivalent Action = EQ equal to ^= NE not equal to > GT greater than < LT less than >= GE greater than or equal to <= LE less than or equal to IN equal to one of a listLogical or Boolean operators are used to link sequences of comparisons.

8 Symbol Mnemonic Equivalent & AND | OR ^ NOTO ther operators Symbol Description > < Maximum <> Minimum || Concatenation5-13v Examples:Assigning constants:N=4;numeric constantSEX= FEMALE ;character constantBasic arithmetic operators:X2=X;copy the valueSUM=X+Y;additionDIF=X-Y;subtraction TWICE=X*2;multiplicationHALF=X/2;divisio nCUBIC=X**3;exponentiationY=-X;change signComparison operators: X<Y THEN C=5 ; (X<Y) ; NAME= PAT ; IF 5<=AGE<=20 ; IF AGE IN(10,20,30) THEN X=5 ; IF SEX IN( M , F ) THEN S=1 ;Logical operators:IF A<B AND C>0 ;IF X=2 OR X=4 ;IF NOT(X=2) ;IF NOT(NAME= SMITH );Other operators:X= A> <B ;NAME= FIRST || LAST ;5-14 Complex expressionsPriority of evaluation: ( ) ** | * / | | + - |A=X+Y+Z;left to rightA=X+Y*Z;operator precedenceA=X/Y/Z; left to rightA=X/(Y/Z);parentheticalZ=ABS(SQRT(X )-2);parentheticalExamples of Assignment Statement1 data ;2 SET ;3 AGE=AGE*12;4 QUETELET= (( ) / ((HT* ) **1)) *10000;5 OUTPUT;6 RETURN;7 RUN;NOTE:Missing values were generated as a result of performing an operation on missing place is given by: (Number of times) at (Line) : (Column).

9 1 at 16:61 at 17:132 at 17:23 NOTE:The data set has 6 observations and 6 :The data statement used seconds. 8 9 PROC PRINT data = ;10 TITLE1 CREATING VARIABLES WITH ASSIGNMENT STATEMENTS ;11 RUN;NOTE: The PROCEDURE PRINT used VARIABLES WITH ASSIGNMENT STATEMENTSOBS NAME SEX AGE HT WT QUETELET1 CHRISTIANSEN M 444 71 195 HOSKING J M 372 70 160 HELMS R M 492 74 195 PIGGY M F . 48 ..5 FROG K M 36 12 1 GONZO 168 25 45 :v General form of a SAS function:variable=function-name(argument 1, argument2, ..);v Each argument is separated from the others by a comma. Must functions accept arguments that areconstants,variables, expressions, or Examples:S=SQRT(X);A=ABS(X);B=MAX(2,7);C =SUBST( INSIDE ,3,4); D=MIN(X,7,A+B).

10 V Types of functionsv Arithmetic (absolute value, square root, mean, )v Trigonometric (cosine, sine, arc )v Other mathematical and statistical (natural logarithm, )v Pseudo-random number generatorsv Character string functionsv Selected functions that compute simple statisticsv Sum sumv Mean meanv Var variancev Min minimumv Max maximumv Std standard deviationv N number non-missingv Nmiss number missingv Simple statistics functions compute statistics for each observation (row) in the SAS data set(functions operate across rows)v Procedures produce statistics for variables (columns) in the SAS data set (procedures operatedown columns)5-16 Subsetting ObservationsA common type of transformation is subsetting observations, creating a new SAS data set with the samevariables as the input data set, but only those observations that satisfy some selection criterion.