DATA Step, Macro, Functions and more

Conditionally summing over fields in a dataset

Reply
Super Contributor
Posts: 261

Conditionally summing over fields in a dataset

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 . . . . .
Super User
Posts: 6,771

Re: Conditionally summing over fields in a dataset

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.

Super Contributor
Posts: 261

Re: Conditionally summing over fields in a dataset

Posted in reply to Astounding

Many thanks Astounding.

Super Contributor
Posts: 261

Re: Conditionally summing over fields in a dataset

Posted in reply to Astounding

Many thanks Astounding.

Ask a Question
Discussion stats
  • 3 replies
  • 125 views
  • 0 likes
  • 2 in conversation