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;