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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.