DATA Step, Macro, Functions and more

Aggregate quarter data and create a new row on year wise sum

Reply
Contributor
Posts: 47

Aggregate quarter data and create a new row on year wise sum

i have my input data like shown below, i want to sum quarter level data (q1 , q2, q3, q4) and create a new row on yearwise

 

RegionCountryProductSubProductScenarioTime PeriodNet Loss IncuredNet Premium EarnedNet CommissionsCAT Losses
APACAustraliaAutoAuto Non-PCGREPORTED ACTUALS/FORECAST2014Q1$68$68($17)($17)
APACAustraliaAutoAuto Non-PCGREPORTED ACTUALS/FORECAST2014Q2($19)$68$68($17)
APACAustraliaAutoAuto Non-PCGREPORTED ACTUALS/FORECAST2014Q3($17)($17)($17)($17)
APACAustraliaAutoAuto Non-PCGREPORTED ACTUALS/FORECAST2014Q4($89)$68($17)($17)
APACAustraliaOtherOtherREPORTED ACTUALS/FORECAST2014Q1$2$0$0$1
APACAustraliaOtherOtherREPORTED ACTUALS/FORECAST2014Q2$0$5$6$2
APACAustraliaOtherOtherREPORTED ACTUALS/FORECAST2014Q3$0$5$7$0
APACAustraliaOtherOtherREPORTED ACTUALS/FORECAST2014Q4$0$0$0$0

 

I want my output to be :

 

 

RegionCountryProductSubProductScenarioTime PeriodNet Loss IncuredNet Premium EarnedNet CommissionsCAT Losses
APACAustraliaAutoAuto Non-PCGREPORTED ACTUALS/FORECAST2014($58)$187$17($69)
APACAustraliaOtherOtherREPORTED ACTUALS/FORECAST2014210133
Super User
Posts: 7,761

Re: Aggregate quarter data and create a new row on year wise sum

Create a new variable (substr(time_period,1,4)), and use that (along with the other group columns) in a class or by with proc summary.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,761

Re: Aggregate quarter data and create a new row on year wise sum

Or adapt the solution from https://communities.sas.com/t5/Base-SAS-Programming/add-new-row-which-will-aggregate-product-subcate..., but omit the first output statement.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 2 replies
  • 51 views
  • 0 likes
  • 2 in conversation