SQL PROCEDURES

[nenè]

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

[Reeza]

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. 

[ChrisBrooks]

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.

[HB]

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?