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;