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
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 |
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.