topic Re: Compare first row with next row until primary field is matching and find MAX value in SAS Programming

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

PV15 — Fri, 04 Dec 2020 05:25:44 GMT

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

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

andreas_lds — Fri, 04 Dec 2020 05:54:31 GMT

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.

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

PV15 — Fri, 04 Dec 2020 06:00:56 GMT

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.

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

andreas_lds — Fri, 04 Dec 2020 06:14:51 GMT

@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!