Conditionally summing over fields in a dataset

brophymj — Fri, 18 Aug 2017 10:42:40 GMT

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	.	.	.	.	.

Re: Conditionally summing over fields in a dataset

Astounding — Fri, 18 Aug 2017 10:53:02 GMT

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.

Re: Conditionally summing over fields in a dataset

brophymj — Fri, 18 Aug 2017 11:21:02 GMT

Many thanks Astounding.

Re: Conditionally summing over fields in a dataset

brophymj — Fri, 18 Aug 2017 12:55:05 GMT

Many thanks Astounding.

topic Re: Conditionally summing over fields in a dataset in SAS Programming

Conditionally summing over fields in a dataset

Re: Conditionally summing over fields in a dataset

Re: Conditionally summing over fields in a dataset

Re: Conditionally summing over fields in a dataset