ASSET TIPE | DOB | VEHICLE_AGE | VEHICLE COST | REG_DATE | SNAPSHOT_DATE | TERM |
NEW | 10/09/81 | 0 | 15000 | 01/03/14 | 01/04/14 | 12 |
USED | 15/08/86 | 6 | 10000 | 01/09/13 | 01/04/14 | 12 |
NEW | 26/11/92 | 0 | 20000 | 01/03/14 | 01/04/14 | 24 |
USED | 17/02/95 | 18 | 5000 | 01/09/12 | 01/04/14 | 36 |
write sql procedure using dataset_initial to calculate total vehicle cost (TOT_VEH_COST) by ASSETTYPE for vehicles not older than 12.
i think that :
select Assettype,SUM(Vehicle_cost) as TOTAL_COST
from Initial_data
where TERM<=12
group by Assettype;
IS CORRECT??
No, it's not correct.
You're not accounting for the vehicles not older than 12 properly - mostly I think you're using the wrong variable.
It looks to me like reg_date is probably the correct variable to use as term is most likely the term of a loan issued to buy the vehicle.
You don't really provide enough information.
Vehicles not older than 12? Is that records with a vehicle_age value of less than or equal to 12? Or is it Term vlaues? Or is it current date minus reg_date? Or is it something else?
Let's assume a term amount <= 12.
So with data like
data stuff;
input ASSET_TYPE $4. VEHICLE_COST TERM;
datalines;
NEW 15000 12
USED 10000 12
NEW 20000 24
USED 5000 36
NEW 15000 6
NEW 15000 9
USED 10000 6
;
run;
You can use the SQL you posted
proc sql;
create table cost_by_asset_type as
select asset_type, sum(vehicle_cost)
from stuff
where term <=12
group by asset_type;
quit;
and come up with
ASSET_TYPE | _TEMG001 |
NEW | 45000 |
USED | 20000 |
Is this what you want?
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.