Lag function in a by group processing?

[dunga]

Hi SAS forum,

I have this dataset which shows the income of two cities in different dates.

data have;

input City $ 1-5 date income;

cards;

tokyo 20150324 10000000

tokyo 20150412 75000000

tokyo 20150522 65000000

miami 20150225 50000000

miami 20150312 60000000

;

run;

Q: I wanted to calculate the income differnece in each city between two adjoining months.

My attempt is this.

data want;

set have;

lag_income = lag(income);

monthly_income_dif= income - lag_income;

run;

Output table:

City	date	income	lag_income	monthly_income_dif
tokyo	20150324	10000000
tokyo	20150412	75000000	10000000	65000000
tokyo	20150522	65000000	75000000	-10000000
miami	20150225	50000000	65000000	-15000000
miami	20150312	60000000	50000000	10000000

Problem: The value       -15000000 in "Monthly_income_dif" variable is not correct because it substract tokyo income from Miami income which is not correct.

How to avoid this problem and calculate the income differences within a city?

Thanks

Mirisa

[rivieralad · Accepted solution]

Hi Mirisa

A quick solution, sort the data by city and date and then set the difference to 0 for the first occurrence of each city...

proc sort data = have;
by city date;
run;
data want;
set have;
by city;
lag_income = lag(income);
if first.city then
monthly_income_dif=0;
else
monthly_income_dif= income - lag_income;
run;

HTH
Chris

[grace999]

I just ran the above code, and got the lag not by city, but just by the whole data. why? The 3rd observation should have missing as the value of lag_income since it's another city, tokyo, but the result got from the income from miami. Why can't I get the lag by group? 

City	date	income	lag_income	monthly_income_dif
miami	20150225	50000000	.	0
miami	20150312	60000000	50000000	10000000
tokyo	20150324	10000000	60000000	0
tokyo	20150412	75000000	10000000	65000000
tokyo	20150522	65000000	75000000	-10000000

[RW9]

Add to the if statement:

data have;
input City $ 1-5 date income;
cards;
tokyo 20150324 10000000
tokyo 20150412 75000000
tokyo 20150522 65000000
miami 20150225 50000000
miami 20150312 60000000
;
run;
proc sort data=have;
 by city date;
run;
data want;
 set have;
 by city;
 retain lst_income;
 if not first.city then monthly_income_dif= income - lst_income;
 lst_income=income;
run;