datesasleft-joinaccumulate

Cumulative loan data with dates for a given firm in SAS

I'm working with a dataset dealscan and attempting to combine the tranche_amount for a given firm, subject to active date and maturity date, so I have a sum of total over a given period.

I have used left join

proc sql;

create table loancumulative as select a.startdate, sum(a.tranche_amount) as tranche_amount from dealscan as a left join dealscan as b on a.startdate >= b.startdate and a.startdate < b.enddate group by a.startdate order by a.startdate;

quit;

However need to also add by companyid, as I am running this across multiple firms .

Any comments?

Regards,

Seb

I added the perm_id as the company identifier in the tranche_amount some issues here?

Regards

Solution

  • According to GROUP BY Syntax, all fields included in the select must be present in the group by clause except for the aggregated fields.

    For example: SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;

    In your case, I think your query should be:

    create table loancumulative as 
 select a.startdate, a.perm_id
      , sum(a.tranche_amount) as tranche_amount 
 from dealscan as a 
 left join dealscan as b 
 on a.startdate >= b.startdate and a.startdate < b.enddate 
 group by a.startdate, a.perm_id 
 order by a.startdate, a.perm_id
;