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
Region | Country | Product | SubProduct | Scenario | Time Period | Net Loss Incured | Net Premium Earned | Net Commissions | CAT Losses |
APAC | Australia | Auto | Auto Non-PCG | REPORTED ACTUALS/FORECAST | 2014Q1 | $68 | $68 | ($17) | ($17) |
APAC | Australia | Auto | Auto Non-PCG | REPORTED ACTUALS/FORECAST | 2014Q2 | ($19) | $68 | $68 | ($17) |
APAC | Australia | Auto | Auto Non-PCG | REPORTED ACTUALS/FORECAST | 2014Q3 | ($17) | ($17) | ($17) | ($17) |
APAC | Australia | Auto | Auto Non-PCG | REPORTED ACTUALS/FORECAST | 2014Q4 | ($89) | $68 | ($17) | ($17) |
APAC | Australia | Other | Other | REPORTED ACTUALS/FORECAST | 2014Q1 | $2 | $0 | $0 | $1 |
APAC | Australia | Other | Other | REPORTED ACTUALS/FORECAST | 2014Q2 | $0 | $5 | $6 | $2 |
APAC | Australia | Other | Other | REPORTED ACTUALS/FORECAST | 2014Q3 | $0 | $5 | $7 | $0 |
APAC | Australia | Other | Other | REPORTED ACTUALS/FORECAST | 2014Q4 | $0 | $0 | $0 | $0 |
I want my output to be :
Region | Country | Product | SubProduct | Scenario | Time Period | Net Loss Incured | Net Premium Earned | Net Commissions | CAT Losses |
APAC | Australia | Auto | Auto Non-PCG | REPORTED ACTUALS/FORECAST | 2014 | ($58) | $187 | $17 | ($69) |
APAC | Australia | Other | Other | REPORTED ACTUALS/FORECAST | 2014 | 2 | 10 | 13 | 3 |
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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.