I have tables on SAS with the configuration below , although with many more variables:
date var12 var41
2013M8 -25.6 -30
2013M9 -24.5 -27.3
2013M10 -26.4
-25.7
2013M11 -32.6 -29.2
2013M12 -30.7 -27.3
2014M01 -29.2 -30.3
I'm trying to put them like this:
var14(t0) var14(t-1) var14(t-2) var41(t0) var41(t-1) var41(t-2)
-29.2 -30.7 -32.6 -30.3 -27.3 -29.2
-30.7 -32.6 -26.4 -27.3 -29.2 -25.7
-32.6 -26.4 -24.5 -29.2 -25.7 -27.3
-26.4 -24.5 -25.6 -25.7 -27.3 -30
This is part of the autoregression , where there is a case transform. I've checked internet on how to do that but not with a stellar success so far.
Please note I'm not trying to do an auto regression , only transforming the data to apply a method later on.
Cheers.
Use the LAG function: var14_1 = lag1(var14); var14_2 = lag2(var14); ....
Note with proc autoreg, you don't need this type of data structure.
PG
Hi PGstats,
Thanks for your answer.
I've tried to automate that but it is not that easy. : /
Cheers,
Andy
It looks like you need a QUEUE .
data have; input date $ var14 var41 ; cards; 2013M8 -25.6 -30 2013M9 -24.5 -27.3 2013M10 -26.4 -25.7 2013M11 -32.6 -29.2 2013M12 -30.7 -27.3 2014M01 -29.2 -30.3 ; run; data have; set have; n+1; run; proc sort data=have ;by descending n;run; data want; set have; array a {3} _temporary_; array b {3} _temporary_; a{1}=a{2};a{2}=a{3}; a{3}=var14; b{1}=b{2};b{2}=b{3}; b{3}=var41; if _n_ ge 3 then do; var14_t_0=a{1}; var14_t_1=a{2}; var14_t_2=a{3}; var41_t_0=b{1}; var41_t_1=b{2}; var41_t_2=b{3}; output; end; keep var14_: var41_:; run;
Xia Keshan
Hi Xia,
The queue solution is great.
However, do you think there is something easier to use , especially with the merge command line?
I'm asking around as I need to do something but I'm trying to do it with the least steps possible.
Cheers.
I don't think you can use merge command line get it. But you can use ARRAY to get it more simple and easy.
data have; input date $ var14 var41 ; cards; 2013M8 -25.6 -30 2013M9 -24.5 -27.3 2013M10 -26.4 -25.7 2013M11 -32.6 -29.2 2013M12 -30.7 -27.3 2014M01 -29.2 -30.3 ; run; %let dsid=%sysfunc(open(have)); %let nobs=%sysfunc(attrn(&dsid,nlobs)); %let dsid=%sysfunc(close(&dsid)); %put &nobs ; data want; set have end=last; array a{&nobs} _temporary_; array b{&nobs} _temporary_; a{_n_}=var14; b{_n_}=var41; if last then do; do i=&nobs to &nobs-3 by -1; var14_t_0=a{i}; var14_t_1=a{i-1}; var14_t_2=a{i-2}; var41_t_0=b{i}; var41_t_1=b{i-1}; var41_t_2=b{i-2}; output; end; end; keep var14_: var41_:; run;
Xia Keshan
After some time on it, I did the following:
data have; input date $ var12 var41; datalines; 2013M8 -25.6 -30 2013M9 -24.5 -27.3 2013M10 -26.4 -25.7 2013M11 -32.6 -29.2 2013M12 -30.7 -27.3 2014M01 -29.2 -30.3 ;;;; run;
/*we change the date*/
data a_want; set have; nouv_date=mdy(substr(date,6,2),01,substr(date,1,4)); format nouv_date monyy5.; drop date; run;
/ *we do it for a1 to a6*/
data a1; set a_want; run;
/* we do it for a1 to a6*/
data a6; set a5; proc sql; delete from a6 where nouv_date=(select max(nouv_date) from a6); quit; data a; merge a1(rename=(var12=var12_t0 var41=var41_t_0)) a2(rename=(var12=var12_t_1 var41=var41_t_1)) a3(rename=(var12=var12_t_2 var41=var41_t_2)) a4(rename=(var12=var12_t_3 var41=var41_t_3)) a5(rename=(var12=var12_t_4 var41=var41_t_4)) a6(rename=(var12=var12_t_5 var41=var41_t_5)); by nouv_date; run;
I wanted to put that, in case there will be people who will have a look. It is a bit different from what I've asked first hand but anyway. I hope it will help.
You can imagine as well that some part of it still has to be automated but it is a different part.
Maybe not the most elegant but I did piece by piece, with your helps.
Cheers.
Ps: Thanks PGstats 老师 and Keshian 老师
???? You are Chinese guy ? You code is very low efficient . I wouldn't use that.
Yes I am.
I know there are better ways but it is a long story
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.