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?
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 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.