BookmarkSubscribeRSS Feed
andy_wk
Calcite | Level 5

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.

8 REPLIES 8
PGStats
Opal | Level 21

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

PG
andy_wk
Calcite | Level 5

Hi PGstats,

Thanks for your answer.

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

Cheers,

Andy

Ksharp
Super User

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
Calcite | Level 5

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
Super User

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
Calcite | Level 5

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
Super User

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

andy_wk
Calcite | Level 5

Yes I am.

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1173 views
  • 6 likes
  • 3 in conversation