Examples: Processing BY groups with FIRST. and LAST. variables in the DATA step

FIRST. and LAST. variables are temporary variables automatically created by SAS^©, representing the beginning and end of each BY group. You can reference FIRST. and LAST. variables in the DATA step, but they are not part of the output dataset. The automatic variable _N_ is used as a counter for DATA step iterations, visible in the log. The IF/THEN statement conditionally executes statements, allowing for specific processing at the beginning or end of BY groups. The value of FIRST. and LAST. variables depends on the sort order of the BY variables. A change in a previous value can affect the value of FIRST.variable, even if the current value of the variable remains the same.

Data Analysis

Type : CREATION_INTERNE

The examples use generated data (datalines) or SASHELP.

1 Code Block

DATA STEP / PROC SORT Data

Explanation :
This example shows how SAS uses the automatic FIRST.variable and LAST.variable to mark the beginning and end of BY groups. FIRST. and LAST. variables are temporary variables automatically created by SAS, representing the beginning and end of each BY group. You can reference FIRST. and LAST. variables in the DATA step, but they are not part of the output dataset. The automatic variable _N_ is used as a counter for DATA step iterations, visible in the log.

Copied!

1	DATA zip;
2	INPUT State $ City $ ZipCode Street $20-29;
3	DATALINES;
4	FL Miami 33133 Rice St
5	FL Miami 33133 Thomas Ave
6	FL Miami 33133 Surrey Dr
7	FL Miami 33133 Trade Ave
8	FL Miami 33146 Nervia St
9	FL Miami 33146 Corsica St
10	FL Lakeland 33801 French Ave
11	FL Lakeland 33809 Egret Dr
12	AZ Tucson 85730 Domenic Ln
13	AZ Tucson 85730 Gleeson Pl
14	;
15	PROC SORT DATA=zip;
16	BY State City ZipCode;
17	RUN;
18
19	DATA zip2;
20	SET zip;
21	BY State City ZipCode;
22	put _n_= City State ZipCode
23	first.city= last.city=
24	first.state= last.state=
25	first.ZipCode= last.ZipCode= ;
26	RUN;

2 Code Block

DATA STEP / PROC SORT Data

Explanation :
Each BY variable creates temporary variables: FIRST.State, LAST.State, FIRST.City, LAST.City, FIRST.ZipCode, and LAST.ZipCode. FIRST. and LAST. variables are temporary variables automatically created by SAS, representing the beginning and end of each BY group. You can reference FIRST. and LAST. variables in the DATA step, but they are not part of the output dataset. The automatic variable _N_ is used as a counter for DATA step iterations, visible in the log.

Copied!

1	DATA zip;
2	INPUT State $ City $ ZipCode Street $20-29;
3	DATALINES;
4	FL Miami 33133 Rice St
5	FL Miami 33133 Thomas Ave
6	FL Miami 33133 Surrey Dr
7	FL Miami 33133 Trade Ave
8	FL Miami 33146 Nervia St
9	FL Miami 33146 Corsica St
10	FL Lakeland 33801 French Ave
11	FL Lakeland 33809 Egret Dr
12	AZ Tucson 85730 Domenic Ln
13	AZ Tucson 85730 Gleeson Pl
14	;
15	PROC SORT DATA=zip;
16	BY City State ZipCode;
17	RUN;
18
19	DATA zip2;
20	SET zip;
21	BY City State ZipCode;
22	put _n_= City State ZipCode
23	first.city= last.city=
24	first.state= last.state=
25	first.ZipCode= last.ZipCode= ;
26	RUN;

3 Code Block

DATA STEP Data

Explanation :
A change in a previous value can affect the value of the FIRST. variable, even if the current value of the variable remains the same. In this example, the values of the FIRST. and LAST. variables depend on the sort order and the value of the BY variable. For observation 3, the value of FIRST.Y is set to 1 because BLUEBERRY is a new value for Y. This change in Y causes FIRST.Z to be set to 1 as well, even if the value of Z has not changed. FIRST. and LAST. variables are temporary variables automatically created by SAS, representing the beginning and end of each BY group. You can reference FIRST. and LAST. variables in the DATA step, but they are not part of the output dataset. The automatic variable _N_ is used as a counter for DATA step iterations, visible in the log.

Copied!

1	DATA fruit;
2	INPUT x $ y $ 10-18 z $ 21-29;
3	DATALINES;
4	apple banana coconut
5	apple banana coconut
6	apple blueberry citron
7	apricot blueberry citron
8	;
9
10	DATA _null_;
11	SET fruit;
12	BY x y z;
13	IF _N_=1 THEN put 'Grouped by X Y Z';
14	put _n_= x= first.x= last.x= first.y= last.y= first.z= last.z= ;
15	RUN;
16
17	DATA _null_;
18	SET fruit;
19	BY y x z;
20	IF _N_=1 THEN put 'Grouped by Y X Z';
21	put _n_= first.y= last.y= first.x= last.x= first.z= last.z= ;
22	RUN;

4 Code Block

DATA STEP / PROC SORT / PROC PRINT Data

Explanation :
The following example calculates the annual payroll by department. It uses IF-THEN statements and the values of the automatic FIRST.variable and LAST.variable to reset the value of PAYROLL to 0 at the beginning of each BY group and to write an observation after processing the last observation of a BY group. FIRST. and LAST. variables are temporary variables automatically created by SAS. They represent the beginning and end of each BY group. The IF/THEN statement conditionally executes statements.

Copied!

1	DATA salaries;
2	INPUT Department $ Name $ WageCategory $ WageRate;
3	DATALINES;
4	BAD Carol Salaried 20000
5	BAD Elizabeth Salaried 5000
6	BAD Linda Salaried 7000
7	BAD Thomas Salaried 9000
8	BAD Lynne Hourly 230
9	DDG Jason Hourly 200
10	DDG Paul Salaried 4000
11	PPD Kevin Salaried 5500
12	PPD Amber Hourly 150
13	PPD Tina Salaried 13000
14	STD Helen Hourly 200
15	STD Jim Salaried 8000
16	;
17
18	PROC PRINT DATA=salaries;
19	RUN;
20
21	PROC SORT DATA=salaries out=temp;
22	BY Department;
23	RUN;
24
25	DATA budget (keep=Department Payroll);
26	SET temp;
27	BY Department;
28	IF WageCategory='Salaried' THEN YearlyWage=WageRate*12;
29	ELSE IF WageCategory='Hourly' THEN YearlyWage=WageRate*2000;
30
31	IF first.Department THEN Payroll=0;
32	Payroll+YearlyWage;
33	IF last.Department;
34	RUN;
35
36	PROC PRINT DATA=budget;
37	FORMAT Payroll dollar10.;
38	title 'Annual Payroll by Department';
39	RUN;

This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.

Retour à la liste