lecture10数据循环处理.ppt

1、Chapter 10 Processing of Data Iteratively,10、数据循环处理,2,Contents,Section 10.1 Using a Simple DO Loop to Process Data Section 10.2 Performing Operations on Similar Variables Section 10.3 Performating Calculation Until a Condition is met Section 10.4 Sampling a SAS Data Set at Random,Section 10.1 Using

2、a Simple DO Loop to Process Data,Objectives Understand DO loop processing. Eliminate redundant code.,Introduction,DO loops can be used in a variety of ways. You can perform repetitive calculations generate data eliminate redundant code execute SAS code conditionally read data.,Scenario,The MIS Depar

3、tment wants to reduce the amount of written code that produces a data set showing the employees 5% raises over the next three years.,Original Program for WORK.RAISES,data work.raises; set ia.employee_data (keep = emp_salary emp_id); year = 1; new_salary = emp_salary*1.05; output; year = 2; new_salar

4、y = new_salary*1.05; output; year = 3; new_salary = new_salary*1.05; output; run;,DO Loop Syntax,General form of a simple iterative DO loop:,DO index-variable=start TO stop ; SAS statements END;,DO index = start TO stop BY increment; SAS statements END;,Yes,No,DO Loop Processing,.,DO Loop Syntax,do

5、i=2 to 10 by 2;,You can define the values used to increment the loop with start TO stop .,do i=10 to 2 by -2;,do k=3.6 to 3.8 by .05;,2,4,6,8,10,12,10,8,6,4,2,0,3.6,3.65,3.70,3.75,3.80,3.85,.,DO Loop Syntax,Expressions: do z = k to n/10; Dates: do date = 01JAN2002d to 31JAN2002d;,DO Loop Syntax,Gene

6、ral form of a DO loop with a value list: The values in the list can be numeric or character.,DO index-variable = value1, value2, value3; SAS statements END;,DO Loop Syntax,Discrete numeric values separated by commas: do n = 1,5,15,30,60; Character values enclosed in quotes and separated by commas: d

7、o month = JAN,FEB,MAR;,Modified Program for WORK.RAISES,data work.raises; set ia.employee_data (keep=emp_salary emp_id rename=(emp_salary=new_salary);,do year = 1 to 3; new_salary=new_salary*1.05; output; end; run;,Generating Random Number Repetitively,This demonstration illustrates how to use a ran

8、dom number generator to produce random weekday, month, and year by using DO LOOP and OUTPUT statement.,Program: Lec10_ex01.sas,Section 10.2 Performing Operations on Similar Variables,Objectives Create arrays. Use arrays to perform operations on similar variables. Use arrays to create new variables.,

9、Scenario,An employee contributes to a charity every month. International Airlines contributes an additional 25% of the employees contribution. Calculate the companys contribution to the employees charity. Determine the companys percentage of each quarterly contribution.,IA.EMP_CONTRIB,Obs EMP_ID qtr

10、1 qtr2 qtr3 qtr4 1 E00224 $12.00 $33.00 $22.00 . 2 E00367 $35.00 $48.00 $40.00 $30.00 3 E00441 . $63.00 $89.00 $90.00 4 E00587 $16.00 $19.00 $30.00 $29.00 5 E00598 $4.00 $8.00 $6.00 $1.00,WORK.COMP_CONTRIB,Obs EMP_ID qtr1 percent1 qtr2 percent2 1 E00224 $15.00 18% $41.25 49% 2 E00367 $43.75 23% $60.

11、00 31% 3 E00441 . . $78.75 26% 4 E00587 $20.00 17% $23.75 20% 5 E00598 $5.00 21% $10.00 42%,Need for Array Processing,data p_contrib; set ia.emp_contrib; total = sum(of qtr1-qtr4); qtr1 = qtr1*1.25; qtr2 = qtr2*1.25; qtr3 = qtr3*1.25; qtr4 = qtr4*1.25; percent1 = qtr1/total; percent2 = qtr2/total; p

12、ercent3 = qtr3/total; percent4 = qtr4/total; run;,Uses for SAS Arrays,You can use arrays to simplify the code required to perform repetitive calculations create many new variables with the same attributes read data rotate SAS data sets by making variables into observations or observations into varia

13、bles.,What Is a SAS Array?,A SAS array is a collection of SAS variables that are grouped under a single name.,QTR1,EMP_ID,QTR2,.,QTR4,PDV,Array,CONTRIB,Contrib1,Contrib2,.,Contrib4,Defining an Array,The ARRAY statement is used to define a set of variables to process in an identical manner. General f

14、orm of the ARRAY statement (explicit): Example: array contrib4 qtr1-qtr4;,ARRAY array-namedimensions ;,Processing an Array,When i = 1,contrib1 = contrib1 * 1.25,qtr1 = qtr1*1.25,Processing an Array,DO loops are typically used to perform an operation on each element of an array. General form of an ar

15、ray used in a DO loop:,ARRAY array-namen elements; DO index-variable = start TO stop; SAS statements using array-nameindex-variable END;,Processing an Array,Use a DO loop to process each element of an array. Example: array contrib4 qtr1-qtr4; do i = 1 to 4;contribi = contribi * 1.25; end;,Illustrati

16、on of Array Processing,data p_contrib (drop = i); set ia.emp_contrib; array contrib4 qtr1-qtr4; do i = 1 to 4; contribi = contribi * 1.25; end; run;,data p_contrib (drop = i); set ia.emp_contrib; array contrib4 qtr1-qtr4; do i = 1 to 4; contribi = contribi * 1.25; end; run;,PDV,IA.EMP_CONTRIB EMP_ID

17、 qtr1 qtr2 qtr3 qtr4 E00224 $12.00 $33.00 $22.00 . E00367 $35.00 $48.00 $40.00 $30.00 E00441 . $63.00 $89.00 $90.00,.,data p_contrib (drop = i); set ia.emp_contrib; array contrib4 qtr1-qtr4; do i = 1 to 4; contribi = contribi * 1.25; end; run;,PDV,contrib,QTR1,EMP_ID,QTR2,QTR3,QTR4,IA.EMP_CONTRIB EM

18、P_ID qtr1 qtr2 qtr3 qtr4 E00224 $12.00 $33.00 $22.00 . E00367 $35.00 $48.00 $40.00 $30.00 E00441 . $63.00 $89.00 $90.00,data p_contrib (drop = i); set ia.emp_contrib; array contrib4 qtr1-qtr4; do i = 1 to 4; contribi = contribi * 1.25; end; run;,PDV,I,contrib,QTR1,EMP_ID,QTR2,QTR3,QTR4,IA.EMP_CONTRI

19、B EMP_ID qtr1 qtr2 qtr3 qtr4 E00224 $12.00 $33.00 $22.00 . E00367 $35.00 $48.00 $40.00 $30.00 E00441 . $63.00 $89.00 $90.00,data p_contrib (drop = i); set ia.emp_contrib; array contrib4 qtr1-qtr4; do i = 1 to 4; contribi = contribi * 1.25; end; run;,PDV,12,33,22,.,.,E00224,I,contrib,QTR1,EMP_ID,QTR2

20、,QTR3,QTR4,IA.EMP_CONTRIB EMP_ID qtr1 qtr2 qtr3 qtr4 E00224 $12.00 $33.00 $22.00 . E00367 $35.00 $48.00 $40.00 $30.00 E00441 . $63.00 $89.00 $90.00,data p_contrib (drop = i); set ia.emp_contrib; array contrib4 qtr1-qtr4; do i = 1 to 4; contribi = contribi * 1.25; end; run;,PDV,12,33,22,.,1,E00224,I,

21、contrib,QTR1,EMP_ID,QTR2,QTR3,QTR4,IA.EMP_CONTRIB EMP_ID qtr1 qtr2 qtr3 qtr4 E00224 $12.00 $33.00 $22.00 . E00367 $35.00 $48.00 $40.00 $30.00 E00441 . $63.00 $89.00 $90.00,data p_contrib (drop = i); set ia.emp_contrib; array contrib4 qtr1-qtr4; do i = 1 to 4; contrib1 = contrib1 * 1.25; end; run; qt

22、r1 = qtr1*1.25,PDV,15,33,22,.,1,E00224,I,contrib,QTR1,EMP_ID,QTR2,QTR3,QTR4,IA.EMP_CONTRIB EMP_ID qtr1 qtr2 qtr3 qtr4 E00224 $12.00 $33.00 $22.00 . E00367 $35.00 $48.00 $40.00 $30.00 E00441 . $63.00 $89.00 $90.00,data p_contrib (drop = i); set ia.emp_contrib; array contrib4 qtr1-qtr4; do i = 1 to 4;

23、 contribi = contribi * 1.25; end; run;,PDV,15,33,22,.,2,E00224,I,contrib,QTR1,EMP_ID,QTR2,QTR3,QTR4,IA.EMP_CONTRIB EMP_ID qtr1 qtr2 qtr3 qtr4 E00224 $12.00 $33.00 $22.00 . E00367 $35.00 $48.00 $40.00 $30.00 E00441 . $63.00 $89.00 $90.00,data p_contrib (drop = i); set ia.emp_contrib; array contrib4 q

24、tr1-qtr4; do i = 1 to 4; contribi = contribi * 1.25; end; run;,PDV,15,33,22,.,2,E00224,I,contrib,QTR1,EMP_ID,QTR2,QTR3,QTR4,IA.EMP_CONTRIB EMP_ID qtr1 qtr2 qtr3 qtr4 E00224 $12.00 $33.00 $22.00 . E00367 $35.00 $48.00 $40.00 $30.00 E00441 . $63.00 $89.00 $90.00,data p_contrib (drop = i); set ia.emp_c

25、ontrib; array contrib4 qtr1-qtr4; do i = 1 to 4; contrib2 = contrib2 * 1.25; end; run; qtr2 = qtr2*1.25,PDV,15,41.25,22,.,2,E00224,I,contrib,QTR1,EMP_ID,QTR2,QTR3,QTR4,IA.EMP_CONTRIB EMP_ID qtr1 qtr2 qtr3 qtr4 E00224 $12.00 $33.00 $22.00 . E00367 $35.00 $48.00 $40.00 $30.00 E00441 . $63.00 $89.00 $9

26、0.00,data p_contrib (drop = i); set ia.emp_contrib; array contrib4 qtr1-qtr4; do i = 1 to 4; contribi = contribi * 1.25; end; run;,PDV,15,41.25,22,.,3,E00224,I,contrib,QTR1,EMP_ID,QTR2,QTR3,QTR4,IA.EMP_CONTRIB EMP_ID qtr1 qtr2 qtr3 qtr4 E00224 $12.00 $33.00 $22.00 . E00367 $35.00 $48.00 $40.00 $30.0

27、0 E00441 . $63.00 $89.00 $90.00,data p_contrib (drop = i); set ia.emp_contrib; array contrib4 qtr1-qtr4; do i = 1 to 4; contribi = contribi * 1.25; end; run;,PDV,15,41.25,22,.,3,E00224,I,contrib,QTR1,EMP_ID,QTR2,QTR3,QTR4,Continue processing DO loop.,.,IA.EMP_CONTRIB EMP_ID qtr1 qtr2 qtr3 qtr4 E0022

28、4 $12.00 $33.00 $22.00 . E00367 $35.00 $48.00 $40.00 $30.00 E00441 . $63.00 $89.00 $90.00,data p_contrib (drop = i); set ia.emp_contrib; array contrib4 qtr1-qtr4; do i = 1 to 4; contribi = contribi * 1.25; end; run;,PDV,15,41.25,22,.,5,E00224,I,contrib,QTR1,EMP_ID,QTR2,QTR3,QTR4,.,IA.EMP_CONTRIB EMP

29、_ID qtr1 qtr2 qtr3 qtr4 E00224 $12.00 $33.00 $22.00 . E00367 $35.00 $48.00 $40.00 $30.00 E00441 . $63.00 $89.00 $90.00,Partial Final Results,WORK.COMP_CONTRIB EMP_ID qtr1 qtr2 qtr3 qtr4 E00224 $15.00 $41.25 $27.50 . E00367 $43.75 $60.00 $50.00 $37.50 E00441 . $78.75 $111.25 $112.50 E00587 $20.00 $23

30、.75 $37.50 $36.25 E00598 $5.00 $10.00 $7.50 $1.25,Creating Variables with Arrays,You can also use arrays to create new variables and perform similar operations on the new variables. array percent4;,PERCENT1 PERCENT2 PERCENT3 PERCENT4,PERCENT,Processing Arrays that Create New Variables,Use a DO loop

31、to process an array that creates new variables. Example: array contrib4 qtr1-qtr4; array percent4;do j = 1 to 4; percentj = contribj / total; end;,Model Generation with Arrays,This demonstration illustrates the use of arrays, DO LOOP, and Random Number Generating Function to do a simulation study on

32、 a given model.,Program: Lec10_ex02.sas,Section 10.3 Performating Calculation Until a Condition is met,Objectives Understand DO WHILE and DO UNTIL processing. Generate data until a condition is met.,Scenario,To increase employee loyalty, the Human Resources manager guaranteed all employees a 5% incr

33、ease from the current date until their 30th year of service.,Obs EMP_ID hire_date EMP_SALARY 1 E00001 11MAR1972 $25,000 2 E00002 19DEC1983 $27,000 3 E00003 12MAR1985 $120,000 4 E00004 16OCT1989 $42,000 5 E00006 27APR1991 $31,000,Current Data,Scenario,Calculate each employees number of years of servi

34、ce at the company based on todays date. Calculate the employees projected salary over 30 years of service to the company. This salary is based on a 5% cost of living adjustment each year, starting with the current number of years of service to the company up to 30 years. Calculate the exact date, wh

35、ich is 30 years from the date the employee was hired.,WORK.RETIRE,Obs EMP_ID retire_date EMP_SALARY 1 E00001 11MAR2002 $28,941 2 E00002 19DEC2013 $53,458 3 E00003 12MAR2015 $261,945 4 E00004 16OCT2019 $111,439 5 E00006 27APR2021 $90,683,Your Salary After 30 Years of Service,data work.retire (keep =

36、emp_id hire_date emp_salary retire_date); set ia.employee_data (keep = emp_salary emp_id hire_date); service_yrs = year(today() - year(hire_date); do while(service_yrs = 30); emp_salary = emp_salary * 1.05; service_yrs = service_yrs + 1; end; year_30 = year(intnx(year,hire_date,30); retire_date=mdy(

37、month(hire_date),day(hire_date),year_30); format retire_date date9.; run;,Conditional Iterative Processing,General form of the DO WHILE statement:,DO WHILE(expression); SAS statements END;,Conditional Iterative Processing,General form of DO UNTIL statement:,DO UNTIL(expression); SAS statements END;,

38、Illustration of DO WHILE Processing,data work.retire(keep = emp_id hire_date emp_salary retire_date); set ia.employee_data(keep = emp_salary emp_id hire_date); service_yrs = year(today() - year(hire_date); do while(service_yrs = 30); emp_salary = emp_salary * 1.05; service_yrs = service_yrs + 1; end

39、; year_30 = year(intnx(year,hire_date,30); retire_date=mdy(month(hire_date),day(hire_date),year_30); format retire_date date9.; run;,data work.retire(keep=emp_id hire_date emp_salary retire_date); set ia.employee_data(keep=emp_salary emp_id hire_date); service_yrs=year(today()-year(hire_date); do wh

40、ile(service_yrs=30); emp_salary=emp_salary * 1.05; service_yrs=service_yrs+1; end; year_30=year(intnx(year,hire_date,30); retire_date=mdy(month(hire_date),day(hire_date),year_30); format retire_date date9.; run;,E00001,PDV,25000,.,.,EMP_ID,EMP_ SALARY,HIRE_ DATE,SERVICE_YRS,YEAR_30,RETIRE_DATE,year(

41、today()=2000 -year(hire_date)=1972 _ service_yrs= 28,EMP_ID hire_date EMP_SALARY E00001 11MAR1972 25000 E00002 19DEC1983 27000 E00003 12MAR1985 120000 E00004 16OCT1989 42000 E00006 27APR1991 31000,11MAR1972,28,data work.retire(keep=emp_id hire_date emp_salary retire_date); set ia.employee_data(keep=

42、emp_salary emp_id hire_date); service_yrs=year(today()-year(hire_date); do while(service_yrs=30); emp_salary=emp_salary * 1.05; service_yrs=service_yrs+1; end; year_30=year(intnx(year,hire_date,30); retire_date=mdy(month(hire_date),day(hire_date),year_30); format retire_date date9.; run;,E00001,2500

43、0,11MAR1972,28,.,.,EMP_ID,EMP_ SALARY,HIRE_ DATE,SERVICE_YRS,YEAR_30,RETIRE_DATE,PDV,EMP_ID hire_date EMP_SALARY E00001 11MAR1972 25000 E00002 19DEC1983 27000 E00003 12MAR1985 120000 E00004 16OCT1989 42000 E00006 27APR1991 31000,data work.retire(keep=emp_id hire_date emp_salary retire_date); set ia.

44、employee_data(keep=emp_salary emp_id hire_date); service_yrs=year(today()-year(hire_date); do while(service_yrs=30); emp_salary=emp_salary * 1.05; service_yrs=service_yrs+1; end; year_30=year(intnx(year,hire_date,30); retire_date=mdy(month(hire_date),day(hire_date),year_30); format retire_date date9

45、.; run;,E00001,26250,11MAR1972,28,.,.,EMP_ID,EMP_ SALARY,HIRE_ DATE,SERVICE_YRS,YEAR_30,RETIRE_DATE,25000 * 1.05 = 26250,PDV,EMP_ID hire_date EMP_SALARY E00001 11MAR1972 25000 E00002 19DEC1983 27000 E00003 12MAR1985 120000 E00004 16OCT1989 42000 E00006 27APR1991 31000,data work.retire(keep=emp_id hi

46、re_date emp_salary retire_date); set ia.employee_data(keep=emp_salary emp_id hire_date); service_yrs=year(today()-year(hire_date); do while(service_yrs=30); emp_salary=emp_salary * 1.05; service_yrs=service_yrs+1; end; year_30=year(intnx(year,hire_date,30); retire_date=mdy(month(hire_date),day(hire_

47、date),year_30); format retire_date date9.; run;,E00001,26250,11MAR1972,29,.,.,EMP_ID,EMP_ SALARY,HIRE_ DATE,SERVICE_YRS,YEAR_30,RETIRE_DATE,service_yrs = 28 + 1,PDV,EMP_ID hire_date EMP_SALARY E00001 11MAR1972 25000 E00002 19DEC1983 27000 E00003 12MAR1985 120000 E00004 16OCT1989 42000 E00006 27APR19

48、91 31000,data work.retire(keep=emp_id hire_date emp_salary retire_date); set ia.employee_data(keep=emp_salary emp_id hire_date); service_yrs=year(today()-year(hire_date); do while(service_yrs=30); emp_salary=emp_salary * 1.05; service_yrs=service_yrs+1; end; year_30=year(intnx(year,hire_date,30); re

49、tire_date=mdy(month(hire_date),day(hire_date),year_30); format retire_date date9.; run;,E00001,26250,11MAR1972,29,.,.,EMP_ID,EMP_ SALARY,HIRE_ DATE,SERVICE_YRS,YEAR_30,RETIRE_DATE,PDV,Is service_yrs = 30? TRUE,EMP_ID hire_date EMP_SALARY E00001 11MAR1972 25000 E00002 19DEC1983 27000 E00003 12MAR1985

50、 120000 E00004 16OCT1989 42000 E00006 27APR1991 31000,data work.retire(keep=emp_id hire_date emp_salary retire_date); set ia.employee_data(keep=emp_salary emp_id hire_date); service_yrs=year(today()-year(hire_date); do while(service_yrs=30); emp_salary=emp_salary * 1.05; service_yrs=service_yrs+1; e

51、nd; year_30=year(intnx(year,hire_date,30); retire_date=mdy(month(hire_date),day(hire_date),year_30); format retire_date date9.; run;,E00001,27562.50,11MAR1972,29,.,.,EMP_ID,EMP_ SALARY,HIRE_ DATE,SERVICE_YRS,YEAR_30,RETIRE_DATE,PDV,EMP_ID hire_date EMP_SALARY E00001 11MAR1972 25000 E00002 19DEC1983

52、27000 E00003 12MAR1985 120000 E00004 16OCT1989 42000 E00006 27APR1991 31000,data work.retire(keep=emp_id hire_date emp_salary retire_date); set ia.employee_data(keep=emp_salary emp_id hire_date); service_yrs=year(today()-year(hire_date); do while(service_yrs=30); emp_salary=emp_salary * 1.05; servic

53、e_yrs=service_yrs+1; end; year_30=year(intnx(year,hire_date,30); retire_date=mdy(month(hire_date),day(hire_date),year_30); format retire_date date9.; run;,E00001,27562.50,11MAR1972,30,.,.,EMP_ID,EMP_ SALARY,HIRE_ DATE,SERVICE_YRS,YEAR_30,RETIRE_DATE,service_yrs = 29 + 1,PDV,EMP_ID hire_date EMP_SALA

54、RY E00001 11MAR1972 25000 E00002 19DEC1983 27000 E00003 12MAR1985 120000 E00004 16OCT1989 42000 E00006 27APR1991 31000,data work.retire(keep=emp_id hire_date emp_salary retire_date); set ia.employee_data(keep=emp_salary emp_id hire_date); service_yrs=year(today()-year(hire_date); do while(service_yr

55、s=30); emp_salary=emp_salary * 1.05; service_yrs=service_yrs+1; end; year_30=year(intnx(year,hire_date,30); retire_date=mdy(month(hire_date),day(hire_date),year_30); format retire_date date9.; run;,E00001,27562.50,11MAR1972,30,.,.,EMP_ID,EMP_ SALARY,HIRE_ DATE,SERVICE_YRS,YEAR_30,RETIRE_DATE,PDV,Is

56、service_yrs = 30? TRUE,EMP_ID hire_date EMP_SALARY E00001 11MAR1972 25000 E00002 19DEC1983 27000 E00003 12MAR1985 120000 E00004 16OCT1989 42000 E00006 27APR1991 31000,data work.retire(keep=emp_id hire_date emp_salary retire_date); set ia.employee_data(keep=emp_salary emp_id hire_date); service_yrs=y

57、ear(today()-year(hire_date); do while(service_yrs=30); emp_salary=emp_salary * 1.05; service_yrs=service_yrs+1; end; year_30=year(intnx(year,hire_date,30); retire_date=mdy(month(hire_date),day(hire_date),year_30); format retire_date date9.; run;,E00001,28940.63,11MAR1972,30,.,.,EMP_ID,EMP_ SALARY,HI

58、RE_ DATE,SERVICE_YRS,YEAR_30,RETIRE_DATE,27562.50 * 1.05 = 28940.63,PDV,EMP_ID hire_date EMP_SALARY E00001 11MAR1972 25000 E00002 19DEC1983 27000 E00003 12MAR1985 120000 E00004 16OCT1989 42000 E00006 27APR1991 31000,data work.retire(keep=emp_id hire_date emp_salary retire_date); set ia.employee_data(keep=emp_sa