I have a dataset like the one below where one of the fields is latest (which indicates the latest year). I want to calculate the reported losses (rl) for the 2 and 5 year before the latest year. So, for the first row, the 2 year figures would be rl_2015 + rl_2016 and the 5 year would be rl_2012 + rl_2013 + rl_2014 + rl_2015 + rl_2016.
If the latest year was the same for every row this would be very straightforward but it varies so I need to put in some conditional sum based on the latest year. Id there a way to sum based on the value of "Latest" in a row.i.e.
%let x = latest;
rl_2yr = rl_&latest + rl_(&latest-1).
within a datastep,
class | policyno | latest | gwp_2011 | rl_2011 | gwp_2012 | rl_2012 | gwp_2013 | rl_2013 | gwp_2014 | rl_2014 | gwp_2015 | rl_2015 | gwp_2016 | rl_2016 | gwp_2017 | rl_2017 |
ARA | 10 | 2016 | . | . | 12,736.27 | 1663.46 | 15,397.14 | 48108.36 | 19,796.19 | 5306.15 | 27,873.91 | 120494.3 | . | . | . | . |
ARA | 144 | 2017 | . | . | . | . | 2,279.05 | . | 2,255.24 | . | 2,255.24 | . | 2,542.86 | . | . | . |
ARA | 235 | 2016 | . | . | 1,825.71 | . | 1,876.19 | . | 1,912.38 | . | 2,047.62 | . | . | . | . | . |
ARA | 253 | 2016 | . | . | 3,066.67 | . | 2,792.70 | 8700 | 3,340.00 | . | 3,488.57 | . | 4,049.52 | . | . | . |
ARA | 282 | 2016 | . | . | 685.71 | 380 | 685.71 | 20168.29 | 1,276.19 | . | 1,921.90 | . | 1,633.33 | . | . | . |
ARA | 285 | 2016 | . | . | 3,059.60 | . | 2,509.98 | . | 3,683.33 | . | 5,321.53 | . | . | . | . | . |
ARA | 335 | 2017 | . | . | . | . | 1,780.95 | . | 1,780.95 | . | 1,780.95 | . | 1,980.00 | . | . | . |
ARA | 388 | 2017 | . | . | . | . | 2,100.00 | . | 2,907.67 | 2662.61 | 6,671.50 | . | 10,685.71 | 35900 | . | . |
ARA | 39 | 2016 | . | . | 2,420.95 | . | 3,025.71 | 373 | 3,528.10 | . | 3,130.95 | . | . | . | . | . |
No macro language needed ... within a DATA step:
array values {2011:2017} rl_2011 - rl_2017;
rl_2yr = values{latest} + values{latest-1};
rl_5yr = values{latest} + values{latest-1} + values{latest-2} + values{latest-3} + values{latest-4};
There may be a way to abbreviate that final statement, but I'm not able to test that right now.
Many thanks Astounding.
Many thanks Astounding.
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.