topic Re: Creating new data field to count the years in a range in SAS Programming

Creating new data field to count the years in a range

sashelp123 — Fri, 03 Nov 2017 18:12:24 GMT

Hello, I am trying to do something in SAS that I have never done before, and don't know where to start.

To simplify things, my data looks like this:

ID # Start year End Year

1 2000 2002

2 2005 2008

3 2008 2009

4 2006 2016

Ultimately, I want SAS to create a new field for Fiscal year, and a new line for each year between the start year and end year (with all of the same information, except a new year). So if the data range is 5 years, there will be 5 lines of information. Using the example above, SAS would change it to this: so the fiscal year starts with the start year, and goes until the end year for each new ID#

ID # Start year End Year Fiscal year

1 2000 2002 2000

1 2000 2002 2001

1 2000 2002 2002

2 2005 2008 2005

2 2005 2008 2006
2 2005 2008 2007

2 2005 2008 2008

3 2008 2009 2008

3 2008 2009 2009

4 2006 2016 2006

4 2006 2016 2007

4 2006 2016 2008

4 2006 2016 2009

4 2006 2016 2010

4 2006 2016 2011

4 2006 2016 2012

4 2006 2016 2013

4 2006 2016 2014

4 2006 2016 2015

Any insight would be greatly appreciated!

Thank you!

Re: Creating new data field to count the years in a range

Reeza — Fri, 03 Nov 2017 18:13:38 GMT

Post what you've tried so far please.

This is commonly asked, so there are a lot of examples on here.

Re: Creating new data field to count the years in a range

sashelp123 — Fri, 03 Nov 2017 18:22:04 GMT

l'm still digging, I am fairly new to sas so don't have anything yet

Re: Creating new data field to count the years in a range

Reeza — Fri, 03 Nov 2017 18:24:54 GMT

This can get you started then:

data have;
input obs start end;
cards;
1 2002 2005
2 2003 2005
3 2010 2017
;
run;

data want;
set have;

do year=start to end;
output;
end;
run;

Re: Creating new data field to count the years in a range

sashelp123 — Fri, 03 Nov 2017 18:28:20 GMT

thank you for the quick reply! the actual data set is actually about 17,000 records so I need to find a way that the code will start with the start year, and create a new line for each year until the end year, without having to individually write out the start and end years for the records. any thoughts?

Re: Creating new data field to count the years in a range

Reeza — Fri, 03 Nov 2017 18:33:43 GMT

The Have set is for demo. Instead of Have use your actual data and variable names.....