case transform with SAS

[andy_wk]

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.

[PGStats]

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

[andy_wk]

Hi PGstats,

Thanks for your answer.

I've tried to automate that but it is not that easy. : /

Cheers,

Andy

[Ksharp]

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

[andy_wk]

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.

[Ksharp]

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

[andy_wk]

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 老师

[Ksharp]

???? You are Chinese guy ?   You code is very low efficient . I wouldn't use that.

[andy_wk]

Yes I am.

I know there are better ways but it is a long story