Hi,
I am new to SAS and I was hoping I could get some help in this forum.
I would like to know how I can calculate the depreciated/appreciated value based on the value avaiable within each group.
So if I have the following table,
data test;
input id km sale dep_rate;
datalines;
1 10000 0 -0.05
1 20000 0 -0.05
1 30000 200 -0.05
1 40000 0 -0.05
1 50000 0 -0.05
1 60000 0 -0.05
2 10000 0 -0.02
2 20000 0 -0.02
2 30000 500 -0.02
2 40000 0 -0.02
2 50000 0 -0.02
2 60000 0 -0.02
3 10000 0 -0.03
3 20000 0 -0.03
3 30000 300 -0.03
3 40000 0 -0.03
3 50000 0 -0.03
;
run;
I would like to know how to achive the following result.
It calculates the depreciated/appreicated value of each group based on it's depreciation rate and each km group.
ID | KM | SALE | DEP_RATE | OUTCOME |
1 | 10000 | 0 | -0.05 | 220.50 |
1 | 20000 | 0 | -0.05 | 210.00 |
1 | 30000 | 200 | -0.05 | 200.00 |
1 | 40000 | 0 | -0.05 | 190.00 |
1 | 50000 | 0 | -0.05 | 180.50 |
1 | 60000 | 0 | -0.05 | 171.48 |
2 | 10000 | 0 | -0.02 | 520.20 |
2 | 20000 | 0 | -0.02 | 510.00 |
2 | 30000 | 500 | -0.02 | 500.00 |
2 | 40000 | 0 | -0.02 | 490.00 |
2 | 50000 | 0 | -0.02 | 480.20 |
2 | 60000 | 0 | -0.02 | 470.60 |
3 | 10000 | 0 | -0.03 | 318.27 |
3 | 20000 | 0 | -0.03 | 309.00 |
3 | 30000 | 300 | -0.03 | 300.00 |
3 | 40000 | 0 | -0.03 | 291.00 |
3 | 50000 | 0 | -0.03 | 282.27 |
I have a feeling that I need to use retain, loop and lag to find depreciated value first, then sort the other way around to find appreciated value.
Finally use the value from depreicated or appreciated variable as RESULT.
However, I can not figure out how I should utilise those functions to achive my result.....
Any advice would be much appreciated.
Tom
Hmm. Interesting .
data test;
input id km sale dep_rate;
datalines;
1 10000 0 -0.05
1 20000 0 -0.05
1 30000 200 -0.05
1 40000 0 -0.05
1 50000 0 -0.05
1 60000 0 -0.05
2 10000 0 -0.02
2 20000 0 -0.02
2 30000 500 -0.02
2 40000 0 -0.02
2 50000 0 -0.02
2 60000 0 -0.02
3 10000 0 -0.03
3 20000 0 -0.03
3 30000 300 -0.03
3 40000 0 -0.03
3 50000 0 -0.03
;
run;
data temp key(keep=id n sale rename=(n=_n sale=_sale));
set test;
by id;
if first.id then n=0;
n+1;
if sale ne 0 then output key;
output temp;
run;
data want;
merge temp key;
by id;
period=n-_n;
OUTCOME=_sale*(1+dep_rate)**period;
drop _: n period;
run;
I think there is something missing.
How would I know the base value of vehicle at 0 KM?
I have added a row for every id- sale value of vehicle at 0 km
data test;
input id km sale dep_rate;
datalines;
1 0 230 0
1 10000 0 -0.05
1 20000 0 -0.05
1 30000 200 -0.05
1 40000 0 -0.05
1 50000 0 -0.05
1 60000 0 -0.05
2 0 500 0
2 10000 0 -0.02
2 20000 0 -0.02
2 30000 500 -0.02
2 40000 0 -0.02
2 50000 0 -0.02
2 60000 0 -0.02
3 0 400 0
3 10000 0 -0.03
3 20000 0 -0.03
3 30000 300 -0.03
3 40000 0 -0.03
3 50000 0 -0.03
;
run;
data output(drop = last_sale_value);
retain last_sale_value;
set test;
by id;
if first.id then do;
last_sale_value=sale;
outcome=sale;
end;
else do;
if sale > 0 then outcome=sale;
else outcome=round(last_sale_value*sum(1,dep_rate),0.01);
last_sale_value=outcome;
end;
run;
Hmm. Interesting .
data test;
input id km sale dep_rate;
datalines;
1 10000 0 -0.05
1 20000 0 -0.05
1 30000 200 -0.05
1 40000 0 -0.05
1 50000 0 -0.05
1 60000 0 -0.05
2 10000 0 -0.02
2 20000 0 -0.02
2 30000 500 -0.02
2 40000 0 -0.02
2 50000 0 -0.02
2 60000 0 -0.02
3 10000 0 -0.03
3 20000 0 -0.03
3 30000 300 -0.03
3 40000 0 -0.03
3 50000 0 -0.03
;
run;
data temp key(keep=id n sale rename=(n=_n sale=_sale));
set test;
by id;
if first.id then n=0;
n+1;
if sale ne 0 then output key;
output temp;
run;
data want;
merge temp key;
by id;
period=n-_n;
OUTCOME=_sale*(1+dep_rate)**period;
drop _: n period;
run;
Indeed very smart.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.