proc expand data=interpoled_dataout=interpoled_data from=year;
by cluster;
id year2;
convert emp / method=join;
run;
I have two data points for two years (1990 and 2018) for three clusters:
year1 | cluster | emp |
01JAN1990 | Accommodation and Food Svcs | 12868 |
01JAN2018 | Accommodation and Food Svcs | 31869.5 |
01JAN1990 | Advanced Materials | 7477.417 |
01JAN2018 | Advanced Materials | 3339.25 |
01JAN1990 | Agribusiness | 5567.5 |
01JAN2018 | Agribusiness | 3619.5 |
I want to interpolate the data for each cluster from 1990 - 2018, (i.e. 1990, 1991, 1992, ...., 2018) for each cluster, such that:
Accommodation and Food Svcs 1990 emp
Accommodation and Food Svcs 1991 emp = 1990 emp + (2018 emp - 1990 emp) / 29 years
.
.
.
Accommodation and Food Svcs 2017 emp = 2016 emp + (2018 emp - 1990 emp) / 29 years
Accommodation and Food Svcs 2018 emp
Advance Materials 1990 emp
Advance Materials 1991 emp = 1990 emp + (2018 emp - 1990 emp) / 29 years
.
.
.
Advance Materials 2017 emp = 2016 emp + (2018 emp - 1990 emp) / 29 years
Advance Materials 2018 emp
Agribusiness 1990 emp
Agribusiness 1991 emp = 1990 emp + (2018 emp - 1990 emp) / 29 years
.
.
.
Agribusiness 2017 emp = 2016 emp + (2018 emp - 1990 emp) / 29 years
Agribusiness 2018 emp
Desired result:
year1 | cluster | emp |
01JAN1990 | Accommodation and Food Svcs | 12868 |
01JAN1991 | Accommodation and Food Svcs | 13523.22 |
01JAN1992 | Accommodation and Food Svcs | 14178.45 |
01JAN1993 | Accommodation and Food Svcs | 14833.67 |
01JAN1994 | Accommodation and Food Svcs | 15488.9 |
01JAN1995 | Accommodation and Food Svcs | 16144.12 |
01JAN1996 | Accommodation and Food Svcs | 16799.34 |
01JAN1997 | Accommodation and Food Svcs | 17454.57 |
01JAN1998 | Accommodation and Food Svcs | 18109.79 |
01JAN1999 | Accommodation and Food Svcs | 18765.02 |
01JAN2000 | Accommodation and Food Svcs | 19420.24 |
01JAN2001 | Accommodation and Food Svcs | 20075.47 |
01JAN2002 | Accommodation and Food Svcs | 20730.69 |
01JAN2003 | Accommodation and Food Svcs | 21385.91 |
01JAN2004 | Accommodation and Food Svcs | 22041.14 |
01JAN2005 | Accommodation and Food Svcs | 22696.36 |
01JAN2006 | Accommodation and Food Svcs | 23351.59 |
01JAN2007 | Accommodation and Food Svcs | 24006.81 |
01JAN2008 | Accommodation and Food Svcs | 24662.03 |
01JAN2009 | Accommodation and Food Svcs | 25317.26 |
01JAN2010 | Accommodation and Food Svcs | 25972.48 |
01JAN2011 | Accommodation and Food Svcs | 26627.71 |
01JAN2012 | Accommodation and Food Svcs | 27282.93 |
01JAN2013 | Accommodation and Food Svcs | 27938.16 |
01JAN2014 | Accommodation and Food Svcs | 28593.38 |
01JAN2015 | Accommodation and Food Svcs | 29248.6 |
01JAN2016 | Accommodation and Food Svcs | 29903.83 |
01JAN2017 | Accommodation and Food Svcs | 30559.05 |
01JAN2018 | Accommodation and Food Svcs | 31869.5 |
01JAN1990 | Advanced Materials | 7477.417 |
01JAN1991 | Advanced Materials | 7334.721 |
01JAN1992 | Advanced Materials | 7192.026 |
01JAN1993 | Advanced Materials | 7049.33 |
01JAN1994 | Advanced Materials | 6906.635 |
01JAN1995 | Advanced Materials | 6763.94 |
01JAN1996 | Advanced Materials | 6621.244 |
01JAN1997 | Advanced Materials | 6478.549 |
01JAN1998 | Advanced Materials | 6335.853 |
01JAN1999 | Advanced Materials | 6193.158 |
01JAN2000 | Advanced Materials | 6050.463 |
01JAN2001 | Advanced Materials | 5907.767 |
01JAN2002 | Advanced Materials | 5765.072 |
01JAN2003 | Advanced Materials | 5622.376 |
01JAN2004 | Advanced Materials | 5479.681 |
01JAN2005 | Advanced Materials | 5336.986 |
01JAN2006 | Advanced Materials | 5194.29 |
01JAN2007 | Advanced Materials | 5051.595 |
01JAN2008 | Advanced Materials | 4908.899 |
01JAN2009 | Advanced Materials | 4766.204 |
01JAN2010 | Advanced Materials | 4623.509 |
01JAN2011 | Advanced Materials | 4480.813 |
01JAN2012 | Advanced Materials | 4338.118 |
01JAN2013 | Advanced Materials | 4195.422 |
01JAN2014 | Advanced Materials | 4052.727 |
01JAN2015 | Advanced Materials | 3910.032 |
01JAN2016 | Advanced Materials | 3767.336 |
01JAN2017 | Advanced Materials | 3624.641 |
01JAN2018 | Advanced Materials | 3339.25 |
01JAN1990 | Agribusiness | 5567.5 |
01JAN1991 | Agribusiness | 5500.328 |
01JAN1992 | Agribusiness | 5433.155 |
01JAN1993 | Agribusiness | 5365.983 |
01JAN1994 | Agribusiness | 5298.81 |
01JAN1995 | Agribusiness | 5231.638 |
01JAN1996 | Agribusiness | 5164.466 |
01JAN1997 | Agribusiness | 5097.293 |
01JAN1998 | Agribusiness | 5030.121 |
01JAN1999 | Agribusiness | 4962.948 |
01JAN2000 | Agribusiness | 4895.776 |
01JAN2001 | Agribusiness | 4828.603 |
01JAN2002 | Agribusiness | 4761.431 |
01JAN2003 | Agribusiness | 4694.259 |
01JAN2004 | Agribusiness | 4627.086 |
01JAN2005 | Agribusiness | 4559.914 |
01JAN2006 | Agribusiness | 4492.741 |
01JAN2007 | Agribusiness | 4425.569 |
01JAN2008 | Agribusiness | 4358.397 |
01JAN2009 | Agribusiness | 4291.224 |
01JAN2010 | Agribusiness | 4224.052 |
01JAN2011 | Agribusiness | 4156.879 |
01JAN2012 | Agribusiness | 4089.707 |
01JAN2013 | Agribusiness | 4022.534 |
01JAN2014 | Agribusiness | 3955.362 |
01JAN2015 | Agribusiness | 3888.19 |
01JAN2016 | Agribusiness | 3821.017 |
01JAN2017 | Agribusiness | 3753.845 |
01JAN2018 | Agribusiness | 3619.5 |
1. Try reducing your problem into something more concise
2. Provide the data as a data step
3. Avoid attaching MS Office files
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.