BookmarkSubscribeRSS Feed
npr
Fluorite | Level 6 npr
Fluorite | Level 6

I have the below table

 

QXY
2005Q13.0961169887314500129.1040486000000000
2005Q23.4104085945044000129.9512334000000000
2005Q33.6573427695665400128.8106877000000000
2005Q42.8444518343448300130.0207229000000000
2006Q12.5487945793408100132.8182149000000000
2006Q22.9662275149639800132.6883096000000000
2006Q31.7026102303690900134.6323000000000000
2006Q42.1814444418781900137.5530985000000000
2007Q12.2305773056005900138.7841262000000000
2007Q21.0829475601426700140.2329980000000000
2007Q32.4291482568206800141.7630128000000000
2007Q42.3145689002407200142.2002388000000000
2008Q13.2065052354548800146.0547948000000000
2008Q24.0342500206461100144.6496579000000000
2008Q32.6762028608582600145.2911509000000000
2008Q40.9543692215926040143.1153248000000000
2009Q1-1.8238815512256100146.1963144000000000
2009Q2-4.6979696255696600139.5495951000000000
2009Q3-4.7265634894500900139.9400998000000000
2009Q4-5.1490781771431700138.4258302000000000
2010Q1-3.2981710949380400138.7433644000000000
2010Q2-0.6637109506627640138.5053422000000000
2010Q3-1.0106688037190800136.1648689000000000
2010Q41.9894873244202500136.0056812000000000
2011Q11.3946441074851600136.5675436000000000
2011Q20.2637516770337850135.7307409000000000
2011Q30.6349998081571580135.0126337000000000
2011Q4-1.8718825520941300135.5315841000000000
2012Q1-1.5735730296375100133.7159334000000000
2012Q20.0737474340454649131.0825881000000000
2012Q30.8757649122138130131.3026443000000000
2012Q42.1949776391215500131.2321800000000000
2013Q12.2888295914611700133.3959875000000000
2013Q22.2423211043327000130.0202857000000000
2013Q31.6493941386791200128.8410652000000000
2013Q41.3714852869855500129.9515952000000000
2014Q11.8515603331309400131.1145455000000000
2014Q21.6826610491274600128.6410867000000000
2014Q31.9375636744922800129.2113542000000000
2014Q42.0727867466260200127.6074090000000000
2015Q12.6981475075623200129.5360682000000000
2015Q21.8309303362247200127.6828525000000000
2015Q32.4591748523656200126.9790237000000000
2015Q42.8082375938319800128.7339383000000000
2016Q12.1797086077527800127.1332319000000000
2016Q22.8992059476779400127.5594986000000000
2016Q32.8316227540859900127.3998496655820000
2016Q42.7991376720679900129.7261627099590000
2017Q13.1665559934047400130.6954598117830000
2017Q22.6113366854055600128.7454621128200000
2017Q32.5752992836911800129.8059871771680000
2017Q42.2356650307510500129.5437067849100000
2018Q11.5478877781360700129.5208997942790000
2018Q21.8491368391964500128.4945852158770000
2018Q31.4698034671504900128.6656376456110000
2018Q41.5943184839162900128.8594970659750000
2019Q11.6437568993936300128.9393215331840000
2019Q21.7462279366323500129.0875669722870000
2019Q31.7565476101035200128.9963390097620000
2019Q41.7562091600998800128.7454621128200000
2020Q11.6097357771958900128.6656376456110000
2020Q21.5710761983067100128.5972166737170000
2020Q31.4759303012403900128.4033572533520000
2020Q41.2901993343276700128.2323048236190000
2021Q11.2299042431696100128.1182698704630000
2021Q21.0807736063708800127.9586209360450000
2021Q31.2187224877945900127.6393230672090000
2021Q41.3542822739175500127.3086217030570000
2022Q11.5519685267146900127.0463413107990000
2022Q21.7069968111048300126.7612539279100000
2022Q31.7187342754862500126.4305525637580000
2022Q41.7076277858093500126.0884477042910000
2023Q11.6364957058010700125.7235358541930000
2023Q21.6577760122577200125.3586240040940000
2023Q31.7207872513338600125.0051156493120000
2023Q41.7387694213015000124.6858177804760000
2024Q11.7825112107623100124.4007303975860000
2024Q21.8435687919111800124.1384500053280000
2024Q31.8556342920661300123.8989766037010000
2024Q41.9276125132277100123.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

 

 

QXY
2005Q13.0961169887314500129.1040486000000000
2005Q23.4104085945044000129.9512334000000000
2005Q33.6573427695665400128.8106877000000000
2005Q42.8444518343448300130.0207229000000000
2006Q12.5487945793408100132.8182149000000000
2006Q22.9662275149639800132.6883096000000000
2006Q31.7026102303690900134.6323000000000000
2006Q42.1814444418781900137.5530985000000000
2007Q12.2305773056005900138.7841262000000000
2007Q21.0829475601426700140.2329980000000000
2007Q32.4291482568206800141.7630128000000000
2007Q42.3145689002407200142.2002388000000000
2008Q13.2065052354548800146.0547948000000000
2008Q24.0342500206461100144.6496579000000000
2008Q32.6762028608582600145.2911509000000000
2008Q40.9543692215926040143.1153248000000000
2009Q1-1.8238815512256100146.1963144000000000
2009Q2-4.6979696255696600139.5495951000000000
2009Q3-4.7265634894500900139.9400998000000000
2009Q4-5.1490781771431700138.4258302000000000
2010Q1-3.2981710949380400138.7433644000000000
2010Q2-0.6637109506627640138.5053422000000000
2010Q3-1.0106688037190800136.1648689000000000
2010Q41.9894873244202500136.0056812000000000
2011Q11.3946441074851600136.5675436000000000
2011Q20.2637516770337850135.7307409000000000
2011Q30.6349998081571580135.0126337000000000
2011Q4-1.8718825520941300135.5315841000000000
2012Q1-1.5735730296375100133.7159334000000000
2012Q20.0737474340454649131.0825881000000000
2012Q30.8757649122138130131.3026443000000000
2012Q42.1949776391215500131.2321800000000000
2013Q12.2888295914611700133.3959875000000000
2013Q22.2423211043327000130.0202857000000000
2013Q31.6493941386791200128.8410652000000000
2013Q41.3714852869855500129.9515952000000000
2014Q11.8515603331309400131.1145455000000000
2014Q21.6826610491274600128.6410867000000000
2014Q31.9375636744922800129.2113542000000000
2014Q42.0727867466260200127.6074090000000000
2015Q13.0961169887314500129.1040486000000000
2015Q23.4104085945044000129.9512334000000000
2015Q33.6573427695665400128.8106877000000000
2015Q42.8444518343448300130.0207229000000000
2016Q12.5487945793408100132.8182149000000000
2016Q22.9662275149639800132.6883096000000000
2016Q31.7026102303690900134.6323000000000000
2016Q42.1814444418781900137.5530985000000000
2017Q12.2305773056005900138.7841262000000000
2017Q21.0829475601426700140.2329980000000000
2017Q32.4291482568206800141.7630128000000000
2017Q42.3145689002407200142.2002388000000000
2018Q13.2065052354548800146.0547948000000000
2018Q24.0342500206461100144.6496579000000000
2018Q32.6762028608582600145.2911509000000000
2018Q40.9543692215926040143.1153248000000000
2019Q1-1.8238815512256100146.1963144000000000
2019Q2-4.6979696255696600139.5495951000000000
2019Q3-4.7265634894500900139.9400998000000000
2019Q4-5.1490781771431700138.4258302000000000
2020Q1-3.2981710949380400138.7433644000000000
2020Q2-0.6637109506627640138.5053422000000000
2020Q3-1.0106688037190800136.1648689000000000
2020Q41.9894873244202500136.0056812000000000
2021Q11.3946441074851600136.5675436000000000
2021Q20.2637516770337850135.7307409000000000
2021Q30.6349998081571580135.0126337000000000
2021Q4-1.8718825520941300135.5315841000000000
2022Q1-1.5735730296375100133.7159334000000000
2022Q20.0737474340454649131.0825881000000000
2022Q30.8757649122138130131.3026443000000000
2022Q42.1949776391215500131.2321800000000000
2023Q12.2888295914611700133.3959875000000000
2023Q22.2423211043327000130.0202857000000000
2023Q31.6493941386791200128.8410652000000000
2023Q41.3714852869855500129.9515952000000000
2024Q11.8515603331309400131.1145455000000000
2024Q21.6826610491274600128.6410867000000000
2024Q31.9375636744922800129.2113542000000000
2024Q42.0727867466260200127.6074090000000000

 

 

any help will be appreciated

4 REPLIES 4
ed_sas_member
Meteorite | Level 14

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;

 

npr
Fluorite | Level 6 npr
Fluorite | Level 6

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.

npr
Fluorite | Level 6 npr
Fluorite | Level 6

for eg 2025 q1 values of X and Y should be similar to that of 2015 q1 and so on ………….

Astounding
PROC Star

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.

SAS Innovate 2025: Register Now

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!

Mastering the WHERE Clause in PROC SQL

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.

Discussion stats
  • 4 replies
  • 681 views
  • 0 likes
  • 3 in conversation