BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tomo230485
Calcite | Level 5

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. 

IDKMSALEDEP_RATEOUTCOME
1100000-0.05220.50
1200000-0.05210.00
130000200-0.05200.00
1400000-0.05190.00
1500000-0.05180.50
1600000-0.05171.48
2100000-0.02520.20
2200000-0.02510.00
230000500-0.02500.00
2400000-0.02490.00
2500000-0.02480.20
2600000-0.02470.60
3100000-0.03318.27
3200000-0.03309.00
330000300-0.03300.00
3400000-0.03291.00
3500000-0.03282.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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

View solution in original post

5 REPLIES 5
RahulG
Barite | Level 11

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;

Tomo230485
Calcite | Level 5
Thank you for your quick reply!
It sort of works but the outcome for the appreciated value seems to be wrong. I guess that is due to relying on the base value you created?
ID:1 KM:20,000 outcome should be 210 (200 * 0.05 + 200).
However your outcomes shows 207.58.
Ksharp
Super User

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;
Tomo230485
Calcite | Level 5
Smart and Simple way!
I was thinking of it in very complicated way.
Thank you!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1107 views
  • 1 like
  • 3 in conversation