BookmarkSubscribeRSS Feed
KRA15
Fluorite | Level 6
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:

 

year1clusteremp
01JAN1990Accommodation and Food Svcs12868
01JAN2018Accommodation and Food Svcs31869.5
01JAN1990Advanced Materials7477.417
01JAN2018Advanced Materials3339.25
01JAN1990Agribusiness5567.5
01JAN2018Agribusiness3619.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:

year1clusteremp
01JAN1990Accommodation and Food Svcs12868
01JAN1991Accommodation and Food Svcs13523.22
01JAN1992Accommodation and Food Svcs14178.45
01JAN1993Accommodation and Food Svcs14833.67
01JAN1994Accommodation and Food Svcs15488.9
01JAN1995Accommodation and Food Svcs16144.12
01JAN1996Accommodation and Food Svcs16799.34
01JAN1997Accommodation and Food Svcs17454.57
01JAN1998Accommodation and Food Svcs18109.79
01JAN1999Accommodation and Food Svcs18765.02
01JAN2000Accommodation and Food Svcs19420.24
01JAN2001Accommodation and Food Svcs20075.47
01JAN2002Accommodation and Food Svcs20730.69
01JAN2003Accommodation and Food Svcs21385.91
01JAN2004Accommodation and Food Svcs22041.14
01JAN2005Accommodation and Food Svcs22696.36
01JAN2006Accommodation and Food Svcs23351.59
01JAN2007Accommodation and Food Svcs24006.81
01JAN2008Accommodation and Food Svcs24662.03
01JAN2009Accommodation and Food Svcs25317.26
01JAN2010Accommodation and Food Svcs25972.48
01JAN2011Accommodation and Food Svcs26627.71
01JAN2012Accommodation and Food Svcs27282.93
01JAN2013Accommodation and Food Svcs27938.16
01JAN2014Accommodation and Food Svcs28593.38
01JAN2015Accommodation and Food Svcs29248.6
01JAN2016Accommodation and Food Svcs29903.83
01JAN2017Accommodation and Food Svcs30559.05
01JAN2018Accommodation and Food Svcs31869.5
01JAN1990Advanced Materials7477.417
01JAN1991Advanced Materials7334.721
01JAN1992Advanced Materials7192.026
01JAN1993Advanced Materials7049.33
01JAN1994Advanced Materials6906.635
01JAN1995Advanced Materials6763.94
01JAN1996Advanced Materials6621.244
01JAN1997Advanced Materials6478.549
01JAN1998Advanced Materials6335.853
01JAN1999Advanced Materials6193.158
01JAN2000Advanced Materials6050.463
01JAN2001Advanced Materials5907.767
01JAN2002Advanced Materials5765.072
01JAN2003Advanced Materials5622.376
01JAN2004Advanced Materials5479.681
01JAN2005Advanced Materials5336.986
01JAN2006Advanced Materials5194.29
01JAN2007Advanced Materials5051.595
01JAN2008Advanced Materials4908.899
01JAN2009Advanced Materials4766.204
01JAN2010Advanced Materials4623.509
01JAN2011Advanced Materials4480.813
01JAN2012Advanced Materials4338.118
01JAN2013Advanced Materials4195.422
01JAN2014Advanced Materials4052.727
01JAN2015Advanced Materials3910.032
01JAN2016Advanced Materials3767.336
01JAN2017Advanced Materials3624.641
01JAN2018Advanced Materials3339.25
01JAN1990Agribusiness5567.5
01JAN1991Agribusiness5500.328
01JAN1992Agribusiness5433.155
01JAN1993Agribusiness5365.983
01JAN1994Agribusiness5298.81
01JAN1995Agribusiness5231.638
01JAN1996Agribusiness5164.466
01JAN1997Agribusiness5097.293
01JAN1998Agribusiness5030.121
01JAN1999Agribusiness4962.948
01JAN2000Agribusiness4895.776
01JAN2001Agribusiness4828.603
01JAN2002Agribusiness4761.431
01JAN2003Agribusiness4694.259
01JAN2004Agribusiness4627.086
01JAN2005Agribusiness4559.914
01JAN2006Agribusiness4492.741
01JAN2007Agribusiness4425.569
01JAN2008Agribusiness4358.397
01JAN2009Agribusiness4291.224
01JAN2010Agribusiness4224.052
01JAN2011Agribusiness4156.879
01JAN2012Agribusiness4089.707
01JAN2013Agribusiness4022.534
01JAN2014Agribusiness3955.362
01JAN2015Agribusiness3888.19
01JAN2016Agribusiness3821.017
01JAN2017Agribusiness3753.845
01JAN2018Agribusiness3619.5
1 REPLY 1
ChrisNZ
Tourmaline | Level 20

1. Try reducing your problem into something more concise

2. Provide the data as a data step

3. Avoid attaching MS Office files

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 373 views
  • 0 likes
  • 2 in conversation