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
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.