I have the below table
Q | X | Y |
2005Q1 | 3.0961169887314500 | 129.1040486000000000 |
2005Q2 | 3.4104085945044000 | 129.9512334000000000 |
2005Q3 | 3.6573427695665400 | 128.8106877000000000 |
2005Q4 | 2.8444518343448300 | 130.0207229000000000 |
2006Q1 | 2.5487945793408100 | 132.8182149000000000 |
2006Q2 | 2.9662275149639800 | 132.6883096000000000 |
2006Q3 | 1.7026102303690900 | 134.6323000000000000 |
2006Q4 | 2.1814444418781900 | 137.5530985000000000 |
2007Q1 | 2.2305773056005900 | 138.7841262000000000 |
2007Q2 | 1.0829475601426700 | 140.2329980000000000 |
2007Q3 | 2.4291482568206800 | 141.7630128000000000 |
2007Q4 | 2.3145689002407200 | 142.2002388000000000 |
2008Q1 | 3.2065052354548800 | 146.0547948000000000 |
2008Q2 | 4.0342500206461100 | 144.6496579000000000 |
2008Q3 | 2.6762028608582600 | 145.2911509000000000 |
2008Q4 | 0.9543692215926040 | 143.1153248000000000 |
2009Q1 | -1.8238815512256100 | 146.1963144000000000 |
2009Q2 | -4.6979696255696600 | 139.5495951000000000 |
2009Q3 | -4.7265634894500900 | 139.9400998000000000 |
2009Q4 | -5.1490781771431700 | 138.4258302000000000 |
2010Q1 | -3.2981710949380400 | 138.7433644000000000 |
2010Q2 | -0.6637109506627640 | 138.5053422000000000 |
2010Q3 | -1.0106688037190800 | 136.1648689000000000 |
2010Q4 | 1.9894873244202500 | 136.0056812000000000 |
2011Q1 | 1.3946441074851600 | 136.5675436000000000 |
2011Q2 | 0.2637516770337850 | 135.7307409000000000 |
2011Q3 | 0.6349998081571580 | 135.0126337000000000 |
2011Q4 | -1.8718825520941300 | 135.5315841000000000 |
2012Q1 | -1.5735730296375100 | 133.7159334000000000 |
2012Q2 | 0.0737474340454649 | 131.0825881000000000 |
2012Q3 | 0.8757649122138130 | 131.3026443000000000 |
2012Q4 | 2.1949776391215500 | 131.2321800000000000 |
2013Q1 | 2.2888295914611700 | 133.3959875000000000 |
2013Q2 | 2.2423211043327000 | 130.0202857000000000 |
2013Q3 | 1.6493941386791200 | 128.8410652000000000 |
2013Q4 | 1.3714852869855500 | 129.9515952000000000 |
2014Q1 | 1.8515603331309400 | 131.1145455000000000 |
2014Q2 | 1.6826610491274600 | 128.6410867000000000 |
2014Q3 | 1.9375636744922800 | 129.2113542000000000 |
2014Q4 | 2.0727867466260200 | 127.6074090000000000 |
2015Q1 | 2.6981475075623200 | 129.5360682000000000 |
2015Q2 | 1.8309303362247200 | 127.6828525000000000 |
2015Q3 | 2.4591748523656200 | 126.9790237000000000 |
2015Q4 | 2.8082375938319800 | 128.7339383000000000 |
2016Q1 | 2.1797086077527800 | 127.1332319000000000 |
2016Q2 | 2.8992059476779400 | 127.5594986000000000 |
2016Q3 | 2.8316227540859900 | 127.3998496655820000 |
2016Q4 | 2.7991376720679900 | 129.7261627099590000 |
2017Q1 | 3.1665559934047400 | 130.6954598117830000 |
2017Q2 | 2.6113366854055600 | 128.7454621128200000 |
2017Q3 | 2.5752992836911800 | 129.8059871771680000 |
2017Q4 | 2.2356650307510500 | 129.5437067849100000 |
2018Q1 | 1.5478877781360700 | 129.5208997942790000 |
2018Q2 | 1.8491368391964500 | 128.4945852158770000 |
2018Q3 | 1.4698034671504900 | 128.6656376456110000 |
2018Q4 | 1.5943184839162900 | 128.8594970659750000 |
2019Q1 | 1.6437568993936300 | 128.9393215331840000 |
2019Q2 | 1.7462279366323500 | 129.0875669722870000 |
2019Q3 | 1.7565476101035200 | 128.9963390097620000 |
2019Q4 | 1.7562091600998800 | 128.7454621128200000 |
2020Q1 | 1.6097357771958900 | 128.6656376456110000 |
2020Q2 | 1.5710761983067100 | 128.5972166737170000 |
2020Q3 | 1.4759303012403900 | 128.4033572533520000 |
2020Q4 | 1.2901993343276700 | 128.2323048236190000 |
2021Q1 | 1.2299042431696100 | 128.1182698704630000 |
2021Q2 | 1.0807736063708800 | 127.9586209360450000 |
2021Q3 | 1.2187224877945900 | 127.6393230672090000 |
2021Q4 | 1.3542822739175500 | 127.3086217030570000 |
2022Q1 | 1.5519685267146900 | 127.0463413107990000 |
2022Q2 | 1.7069968111048300 | 126.7612539279100000 |
2022Q3 | 1.7187342754862500 | 126.4305525637580000 |
2022Q4 | 1.7076277858093500 | 126.0884477042910000 |
2023Q1 | 1.6364957058010700 | 125.7235358541930000 |
2023Q2 | 1.6577760122577200 | 125.3586240040940000 |
2023Q3 | 1.7207872513338600 | 125.0051156493120000 |
2023Q4 | 1.7387694213015000 | 124.6858177804760000 |
2024Q1 | 1.7825112107623100 | 124.4007303975860000 |
2024Q2 | 1.8435687919111800 | 124.1384500053280000 |
2024Q3 | 1.8556342920661300 | 123.8989766037010000 |
2024Q4 | 1.9276125132277100 | 123.6709066973900000 |
I want all values of X and Y from 2015 q1 to be replaced with the values from 2005 q1 to 2014 q4.
The output has to be like below
Q | X | Y |
2005Q1 | 3.0961169887314500 | 129.1040486000000000 |
2005Q2 | 3.4104085945044000 | 129.9512334000000000 |
2005Q3 | 3.6573427695665400 | 128.8106877000000000 |
2005Q4 | 2.8444518343448300 | 130.0207229000000000 |
2006Q1 | 2.5487945793408100 | 132.8182149000000000 |
2006Q2 | 2.9662275149639800 | 132.6883096000000000 |
2006Q3 | 1.7026102303690900 | 134.6323000000000000 |
2006Q4 | 2.1814444418781900 | 137.5530985000000000 |
2007Q1 | 2.2305773056005900 | 138.7841262000000000 |
2007Q2 | 1.0829475601426700 | 140.2329980000000000 |
2007Q3 | 2.4291482568206800 | 141.7630128000000000 |
2007Q4 | 2.3145689002407200 | 142.2002388000000000 |
2008Q1 | 3.2065052354548800 | 146.0547948000000000 |
2008Q2 | 4.0342500206461100 | 144.6496579000000000 |
2008Q3 | 2.6762028608582600 | 145.2911509000000000 |
2008Q4 | 0.9543692215926040 | 143.1153248000000000 |
2009Q1 | -1.8238815512256100 | 146.1963144000000000 |
2009Q2 | -4.6979696255696600 | 139.5495951000000000 |
2009Q3 | -4.7265634894500900 | 139.9400998000000000 |
2009Q4 | -5.1490781771431700 | 138.4258302000000000 |
2010Q1 | -3.2981710949380400 | 138.7433644000000000 |
2010Q2 | -0.6637109506627640 | 138.5053422000000000 |
2010Q3 | -1.0106688037190800 | 136.1648689000000000 |
2010Q4 | 1.9894873244202500 | 136.0056812000000000 |
2011Q1 | 1.3946441074851600 | 136.5675436000000000 |
2011Q2 | 0.2637516770337850 | 135.7307409000000000 |
2011Q3 | 0.6349998081571580 | 135.0126337000000000 |
2011Q4 | -1.8718825520941300 | 135.5315841000000000 |
2012Q1 | -1.5735730296375100 | 133.7159334000000000 |
2012Q2 | 0.0737474340454649 | 131.0825881000000000 |
2012Q3 | 0.8757649122138130 | 131.3026443000000000 |
2012Q4 | 2.1949776391215500 | 131.2321800000000000 |
2013Q1 | 2.2888295914611700 | 133.3959875000000000 |
2013Q2 | 2.2423211043327000 | 130.0202857000000000 |
2013Q3 | 1.6493941386791200 | 128.8410652000000000 |
2013Q4 | 1.3714852869855500 | 129.9515952000000000 |
2014Q1 | 1.8515603331309400 | 131.1145455000000000 |
2014Q2 | 1.6826610491274600 | 128.6410867000000000 |
2014Q3 | 1.9375636744922800 | 129.2113542000000000 |
2014Q4 | 2.0727867466260200 | 127.6074090000000000 |
2015Q1 | 3.0961169887314500 | 129.1040486000000000 |
2015Q2 | 3.4104085945044000 | 129.9512334000000000 |
2015Q3 | 3.6573427695665400 | 128.8106877000000000 |
2015Q4 | 2.8444518343448300 | 130.0207229000000000 |
2016Q1 | 2.5487945793408100 | 132.8182149000000000 |
2016Q2 | 2.9662275149639800 | 132.6883096000000000 |
2016Q3 | 1.7026102303690900 | 134.6323000000000000 |
2016Q4 | 2.1814444418781900 | 137.5530985000000000 |
2017Q1 | 2.2305773056005900 | 138.7841262000000000 |
2017Q2 | 1.0829475601426700 | 140.2329980000000000 |
2017Q3 | 2.4291482568206800 | 141.7630128000000000 |
2017Q4 | 2.3145689002407200 | 142.2002388000000000 |
2018Q1 | 3.2065052354548800 | 146.0547948000000000 |
2018Q2 | 4.0342500206461100 | 144.6496579000000000 |
2018Q3 | 2.6762028608582600 | 145.2911509000000000 |
2018Q4 | 0.9543692215926040 | 143.1153248000000000 |
2019Q1 | -1.8238815512256100 | 146.1963144000000000 |
2019Q2 | -4.6979696255696600 | 139.5495951000000000 |
2019Q3 | -4.7265634894500900 | 139.9400998000000000 |
2019Q4 | -5.1490781771431700 | 138.4258302000000000 |
2020Q1 | -3.2981710949380400 | 138.7433644000000000 |
2020Q2 | -0.6637109506627640 | 138.5053422000000000 |
2020Q3 | -1.0106688037190800 | 136.1648689000000000 |
2020Q4 | 1.9894873244202500 | 136.0056812000000000 |
2021Q1 | 1.3946441074851600 | 136.5675436000000000 |
2021Q2 | 0.2637516770337850 | 135.7307409000000000 |
2021Q3 | 0.6349998081571580 | 135.0126337000000000 |
2021Q4 | -1.8718825520941300 | 135.5315841000000000 |
2022Q1 | -1.5735730296375100 | 133.7159334000000000 |
2022Q2 | 0.0737474340454649 | 131.0825881000000000 |
2022Q3 | 0.8757649122138130 | 131.3026443000000000 |
2022Q4 | 2.1949776391215500 | 131.2321800000000000 |
2023Q1 | 2.2888295914611700 | 133.3959875000000000 |
2023Q2 | 2.2423211043327000 | 130.0202857000000000 |
2023Q3 | 1.6493941386791200 | 128.8410652000000000 |
2023Q4 | 1.3714852869855500 | 129.9515952000000000 |
2024Q1 | 1.8515603331309400 | 131.1145455000000000 |
2024Q2 | 1.6826610491274600 | 128.6410867000000000 |
2024Q3 | 1.9375636744922800 | 129.2113542000000000 |
2024Q4 | 2.0727867466260200 | 127.6074090000000000 |
any help will be appreciated
Hi @npr
You can try this, assuming you have 80 rows and you want to update the second half with the first one.
data want;
set have (rename=(X = X_old Y=Y_old));
if _n_<= 40 then do;
set have (obs=40 keep=X Y);
end;
if _n_> 40 then do;
set have (obs=40 keep=X Y);
end;
drop X_old Y_old;
run;
Hi Ed
If I have more than 80 rows then what do I have to do ? Basically I want the values from 2005 q1 to 2014 q4 to repeat from 2015 q1.
for eg 2025 q1 values of X and Y should be similar to that of 2015 q1 and so on ………….
One approach:
data want;
set have;
if Q = '2005Q1' then firstrow = _n_;
else if Q = '2014Q4' then lastrow = _n_;
else if Q > '2014Q4' then do;
pointer = firstrow + mod(_n_, lastrow - firstrow + 1);
set have (keep=x y) point = pointer;
end;
run;
This definitely assumes you have no gaps in your time sequence ... every row within the range is present.
It's untested code, since you have the data. But see if it works for what you need.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.