Difference in values

[u13161556U]

Hi there,

I was wondering if anyone could help me.

I'm trying to calculate the difference between the first balance and the last balance (greater than zero). eg: I need to get a final answer of 3 (10-7).

Thank you

[tsap]

DATA WORK.HAVE;
FORMAT 	 Account_num $5. Balance 8. Status $1.;
INFORMAT Account_num $5. Balance 8. Status $1.;
INPUT    Account_num	 Balance	Status;
INFILE DATALINES DLM='|' DSD;
DATALINES;
12345|10|x
12345|8|x
12345|7|x
12345|0|y
12345|0|y
;


PROC SQL;
CREATE TABLE WORK.WANT	AS
	SELECT
		  Account_Num
		, Max(Balance)-MIN(Balance)		AS Difference

	FROM WORK.HAVE
	WHERE Balance > 0
	GROUP BY Account_Num;
QUIT;

Output:

Account_num	Difference
12345		3

[koyelghosh]

@u13161556U Actually you can make it 3-4 lines shorter and easier to read. Edited code is as below.

DATA Intermediate;
	SET HAVE;

	IF Balance > 0 THEN
		OUTPUT;
RUN;

DATA Want (DROP=StartBalance Balance);
	SET Intermediate;
	BY Account_Num;
	RETAIN StartBalance 0;
	IF FIRST.Account_Num THEN
		StartBalance=Balance;

	IF LAST.Account_Num THEN DO;
		Difference=Balance - StartBalance;
		OUTPUT;
	END;
RUN;

[koyelghosh]

@u13161556U Here is my two step approach. It works for the limited dataset that you provided. You will have to test on the larger dataset.

DATA Intermediate;
	SET HAVE;

	IF Balance > 0 THEN
		OUTPUT;
RUN;

DATA Want (DROP=StartBalance EndBalance Balance);
	SET Intermediate;
	BY Account_Num;
	RETAIN StartBalance 0;
	RETAIN EndBalance 0;

	IF FIRST.Account_Num THEN
		StartBalance=Balance;

	IF LAST.Account_Num THEN
		EndBalance=Balance;

	IF StartBalance ^=0 AND EndBalance ^=0 THEN
		DO;
			Difference=EndBalance - StartBalance;
			OUTPUT;
		END;
RUN;

Please let me know, how did it work out for you.