Hi,
I came across this problem with table below.
Here Y, M are year and month, and Y2 and M2 are the next month of (Y, M). In each month here are days (totaling 28/29/30/31 depending on month) designated by DY. For each month, I am going to pick one day as the beginning day of the month (DY) and another day as the ending day (DY1) so that DY is rotated. For example, if 5 is picked as the beginning day, 4 would be the ending day, as days follow 5, 6,..., 31, 1, 2, 3, 4 assuming the month has 31 days, and so on. Once the beginning day has been picked for the month (ending day implied), I move to the next month and do the same. At the end, I need to minimize:
sum((MWB(i) - MWE(i+1))**2), where i = 1... # of months.
The example below has 3 months so the total space is 31*30*31. The actual problem involves one year and a much larger space of 31*30*31...*31.
I am considering optmodel but still trying to specify the problem with it. Any help is appreciated!
Y | M | M2 | Y2 | DY | DY1 | MWB | MWE |
2022 | 1 | 2 | 2022 | 1 | 31 | 3,060.79 | 2,847.60 |
2022 | 1 | 2 | 2022 | 2 | 1 | 2,795.53 | 2,755.11 |
2022 | 1 | 2 | 2022 | 3 | 2 | 2,711.12 | 2,723.58 |
2022 | 1 | 2 | 2022 | 4 | 3 | 2,585.14 | 2,357.31 |
2022 | 1 | 2 | 2022 | 5 | 4 | 2,856.69 | 2,336.30 |
2022 | 1 | 2 | 2022 | 6 | 5 | 2,585.89 | 2,314.10 |
2022 | 1 | 2 | 2022 | 7 | 6 | 2,708.57 | 2,537.95 |
2022 | 1 | 2 | 2022 | 8 | 7 | 2,761.23 | 2,327.29 |
2022 | 1 | 2 | 2022 | 9 | 8 | 2,550.63 | 2,352.52 |
2022 | 1 | 2 | 2022 | 10 | 9 | 2,970.52 | 2,354.57 |
2022 | 1 | 2 | 2022 | 11 | 10 | 2,995.84 | 2,281.79 |
2022 | 1 | 2 | 2022 | 12 | 11 | 3,453.13 | 2,612.58 |
2022 | 1 | 2 | 2022 | 13 | 12 | 3,000.57 | 2,619.14 |
2022 | 1 | 2 | 2022 | 14 | 13 | 2,886.60 | 2,875.82 |
2022 | 1 | 2 | 2022 | 15 | 14 | 3,054.25 | 2,646.22 |
2022 | 1 | 2 | 2022 | 16 | 15 | 2,983.17 | 2,611.71 |
2022 | 1 | 2 | 2022 | 17 | 16 | 2,516.30 | 2,709.25 |
2022 | 1 | 2 | 2022 | 18 | 17 | 2,670.58 | 2,623.86 |
2022 | 1 | 2 | 2022 | 19 | 18 | 2,784.30 | 2,254.84 |
2022 | 1 | 2 | 2022 | 20 | 19 | 2,570.44 | 2,331.93 |
2022 | 1 | 2 | 2022 | 21 | 20 | 2,451.75 | 2,354.15 |
2022 | 1 | 2 | 2022 | 22 | 21 | 2,423.53 | 2,283.58 |
2022 | 1 | 2 | 2022 | 23 | 22 | 2,455.70 | 2,130.18 |
2022 | 1 | 2 | 2022 | 24 | 23 | 2,512.75 | 2,028.61 |
2022 | 1 | 2 | 2022 | 25 | 24 | 2,493.90 | 2,104.30 |
2022 | 1 | 2 | 2022 | 26 | 25 | 2,529.80 | 2,213.52 |
2022 | 1 | 2 | 2022 | 27 | 26 | 2,574.74 | 2,228.63 |
2022 | 1 | 2 | 2022 | 28 | 27 | 3,012.91 | 2,277.01 |
2022 | 1 | 2 | 2022 | 29 | 28 | 3,163.53 | 2,289.61 |
2022 | 1 | 2 | 2022 | 30 | 29 | 3,372.30 | 2,690.60 |
2022 | 1 | 2 | 2022 | 31 | 30 | 3,150.71 | 2,840.38 |
2022 | 2 | 3 | 2022 | 1 | 30 | 4,147.38 | 2,937.49 |
2022 | 2 | 3 | 2022 | 2 | 1 | 4,774.03 | 3,289.59 |
2022 | 2 | 3 | 2022 | 3 | 2 | 4,144.49 | 4,371.40 |
2022 | 2 | 3 | 2022 | 4 | 3 | 4,090.07 | 3,824.04 |
2022 | 2 | 3 | 2022 | 5 | 4 | 4,085.97 | 3,807.31 |
2022 | 2 | 3 | 2022 | 6 | 5 | 3,766.41 | 3,786.18 |
2022 | 2 | 3 | 2022 | 7 | 6 | 3,382.19 | 3,770.40 |
2022 | 2 | 3 | 2022 | 8 | 7 | 3,331.66 | 3,463.21 |
2022 | 2 | 3 | 2022 | 9 | 8 | 2,985.52 | 3,040.08 |
2022 | 2 | 3 | 2022 | 10 | 9 | 3,420.95 | 2,888.73 |
2022 | 2 | 3 | 2022 | 11 | 10 | 3,336.96 | 2,751.84 |
2022 | 2 | 3 | 2022 | 12 | 11 | 2,849.19 | 3,120.61 |
2022 | 2 | 3 | 2022 | 13 | 12 | 2,923.18 | 2,870.00 |
2022 | 2 | 3 | 2022 | 14 | 13 | 3,466.61 | 2,598.92 |
2022 | 2 | 3 | 2022 | 15 | 14 | 3,581.33 | 2,630.91 |
2022 | 2 | 3 | 2022 | 16 | 15 | 3,751.99 | 3,145.85 |
2022 | 2 | 3 | 2022 | 17 | 16 | 3,793.83 | 3,315.29 |
2022 | 2 | 3 | 2022 | 18 | 17 | 3,927.19 | 3,409.43 |
2022 | 2 | 3 | 2022 | 19 | 18 | 3,958.74 | 3,521.23 |
2022 | 2 | 3 | 2022 | 20 | 19 | 3,767.45 | 3,619.12 |
2022 | 2 | 3 | 2022 | 21 | 20 | 3,569.70 | 3,727.37 |
2022 | 2 | 3 | 2022 | 22 | 21 | 3,951.63 | 3,494.98 |
2022 | 2 | 3 | 2022 | 23 | 22 | 3,893.76 | 3,311.69 |
2022 | 2 | 3 | 2022 | 24 | 23 | 3,710.34 | 3,664.18 |
2022 | 2 | 3 | 2022 | 25 | 24 | 3,100.67 | 3,606.88 |
2022 | 2 | 3 | 2022 | 26 | 25 | 3,164.81 | 3,345.92 |
2022 | 2 | 3 | 2022 | 27 | 26 | 2,974.90 | 2,768.13 |
2022 | 2 | 3 | 2022 | 28 | 27 | 3,391.46 | 2,798.68 |
2022 | 2 | 3 | 2022 | 29 | 28 | 3,377.84 | 2,738.24 |
2022 | 2 | 3 | 2022 | 30 | 29 | 3,499.97 | 3,046.27 |
2022 | 3 | 4 | 2022 | 1 | 31 | 3,653.04 | 3,733.46 |
2022 | 3 | 4 | 2022 | 2 | 1 | 3,992.26 | 3,705.17 |
2022 | 3 | 4 | 2022 | 3 | 2 | 4,419.02 | 3,384.90 |
2022 | 3 | 4 | 2022 | 4 | 3 | 4,390.42 | 3,612.65 |
2022 | 3 | 4 | 2022 | 5 | 4 | 4,330.79 | 3,914.40 |
2022 | 3 | 4 | 2022 | 6 | 5 | 3,968.64 | 3,965.32 |
2022 | 3 | 4 | 2022 | 7 | 6 | 3,781.22 | 3,805.89 |
2022 | 3 | 4 | 2022 | 8 | 7 | 3,256.24 | 3,613.85 |
2022 | 3 | 4 | 2022 | 9 | 8 | 3,123.72 | 3,506.88 |
2022 | 3 | 4 | 2022 | 10 | 9 | 3,494.57 | 2,894.91 |
2022 | 3 | 4 | 2022 | 11 | 10 | 3,665.44 | 2,818.28 |
2022 | 3 | 4 | 2022 | 12 | 11 | 3,946.23 | 3,105.49 |
2022 | 3 | 4 | 2022 | 13 | 12 | 3,801.13 | 3,436.77 |
2022 | 3 | 4 | 2022 | 14 | 13 | 4,145.82 | 3,608.90 |
2022 | 3 | 4 | 2022 | 15 | 14 | 4,193.16 | 3,579.46 |
2022 | 3 | 4 | 2022 | 16 | 15 | 3,660.22 | 3,740.13 |
2022 | 3 | 4 | 2022 | 17 | 16 | 3,764.65 | 3,778.65 |
2022 | 3 | 4 | 2022 | 18 | 17 | 3,819.29 | 3,409.57 |
2022 | 3 | 4 | 2022 | 19 | 18 | 3,546.83 | 3,455.49 |
2022 | 3 | 4 | 2022 | 20 | 19 | 4,045.83 | 3,596.81 |
2022 | 3 | 4 | 2022 | 21 | 20 | 4,197.98 | 3,244.33 |
2022 | 3 | 4 | 2022 | 22 | 21 | 3,476.33 | 3,635.28 |
2022 | 3 | 4 | 2022 | 23 | 22 | 3,451.32 | 3,780.78 |
2022 | 3 | 4 | 2022 | 24 | 23 | 3,513.11 | 3,002.59 |
2022 | 3 | 4 | 2022 | 25 | 24 | 3,769.57 | 3,064.38 |
2022 | 3 | 4 | 2022 | 26 | 25 | 3,588.74 | 3,116.02 |
2022 | 3 | 4 | 2022 | 27 | 26 | 4,063.67 | 3,503.90 |
2022 | 3 | 4 | 2022 | 28 | 27 | 4,319.61 | 3,308.17 |
2022 | 3 | 4 | 2022 | 29 | 28 | 4,127.35 | 3,703.07 |
2022 | 3 | 4 | 2022 | 30 | 29 | 4,143.08 | 3,796.24 |
2022 | 3 | 4 | 2022 | 31 | 30 | 4,084.32 | 3,711.78 |
An example of what the expected output given a set of conditions for at least some of that data would help.
Thanks for looking into it, Ballardw! Sure -- for example, if I pick
1/14/2022 as begining day for January, implying 1/13/2022 as ending day
MWE = 2875.82
2/12/2022 as begining day for February, implying 2/11/2022 as ending day
MWB = 2849.19 and MWE = 3120.61
3/9/2022 as begining day for March, implying 3/8/2022 as ending day
MWB = 3123.72
So the total distance is (2849.19-2875.82)**2 + (3123.72-3120.61)**2 = 718.829 and is the optima (min).
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!Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.
Find more tutorials on the SAS Users YouTube channel.