Compare first row with next row until primary field is matching and find MAX value

[PV15]

Here is my requirement:

HAVE

EMP ID	SALARY1	SALARY2	QTY
AB1001	100	101	1
AB1001	200	102	2
CD1002	100	101	1
CD1002	200	102	2
CD1002	300	103	3
EF1003	400	101	1
EF1003	500	102	2
GH1004	600	101	1
GH1004	700	102	2
GH1004	800	103	3
GH1004	900	104	4

WANT:

	EMP ID	SALARY1	SALARY2	QTY
	AB1001	100	102	1
	AB1001	200	101	2
New record	AB1001	200	102	3
	CD1002	100	101	1
	CD1002	200	103	2
	CD1002	300	102	3
New record	CD1002	300	103	6
	EF1003	500	101	1
	EF1003	400	102	2
New record	EF1003	500	102	3
	GH1004	600	101	1
	GH1004	700	102	2
	GH1004	900	103	3
	GH1004	800	104	4
New record	GH1004	900	104	10

[andreas_lds]

What have you tried so far?

I would use a data-step with BY-statement and RETAIN to save max-values of the salary-variables and the sum of qty. Checking first.EMP to reset retained variables and last.EMP to output the additional observation.

[PV15]

I have tried using .FIRST and .LAST and saving the value in temp variable. The logic is working fine, if there are only 2 same EMPID's , but my requirement is for more than 2 EMPID's.

[andreas_lds]

---

@PV15 wrote:

I have tried using .FIRST and .LAST and saving the value in temp variable. The logic is working fine, if there are only 2 same EMPID's , but my requirement is for more than 2 EMPID's.

---

Please post the code using the "Insert SAS Code" button!