topic Re: SQL PROCEDURES in SAS Procedures

SQL PROCEDURES

nenè — Sat, 26 Aug 2017 14:32:33 GMT

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

Re: SQL PROCEDURES

Reeza — Sat, 26 Aug 2017 15:15:58 GMT

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.

Re: SQL PROCEDURES

ChrisBrooks — Sat, 26 Aug 2017 21:28:06 GMT

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.

Re: SQL PROCEDURES

HB — Mon, 28 Aug 2017 18:36:53 GMT

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?