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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.