Creating new data field to count the years in a range

Reply
New Contributor
Posts: 4

Creating new data field to count the years in a range

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!

Super User
Posts: 21,546

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

Posted in reply to rcamphaug

Post what you've tried so far please. 

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

New Contributor
Posts: 4

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

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

Super User
Posts: 21,546

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

Posted in reply to rcamphaug

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;
New Contributor
Posts: 4

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

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?

Super User
Posts: 21,546

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

Posted in reply to rcamphaug

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

Ask a Question
Discussion stats
  • 5 replies
  • 110 views
  • 0 likes
  • 2 in conversation