Date: Thu, 14 Apr 2005 21:45:44 -0400
Reply-To: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Subject: Re: lag & lead
You can use SQL and do a self-join to generate both leads and lags.
proc sql;
create table want as
select distinct
have.*,
sum(case when have.year-1=cross.year then cross.x1 else . end)
as lag1x1,
sum(case when have.year-2=cross.year then cross.x1 else . end)
as lag2x1,
sum(case when have.year-3=cross.year then cross.x1 else . end)
as lag3x1,
sum(case when have.year+1=cross.year then cross.x1 else . end)
as lead1x1,
sum(case when have.year+2=cross.year then cross.x1 else . end)
as lead2x1,
sum(case when have.year+3=cross.year then cross.x1 else . end)
as lead3x1,
sum(case when have.year-1=cross.year then cross.x2 else . end)
as lag1x2,
sum(case when have.year-2=cross.year then cross.x2 else . end)
as lag2x2,
sum(case when have.year-3=cross.year then cross.x2 else . end)
as lag3x2,
sum(case when have.year+1=cross.year then cross.x2 else . end)
as lead1x2,
sum(case when have.year+2=cross.year then cross.x2 else . end)
as lead2x2,
sum(case when have.year+3=cross.year then cross.x2 else . end)
as lead3x2
from have inner join have as cross
on have.id=cross.id
group by have.id, have.year
;
quit;
On Tue, 12 Apr 2005 04:04:13 -0400, Thomas <tythong@YAHOO.COM> wrote:
>Hi all,
>
>I know the "lag" function is available to lag the variable for N-period.
>But, I have a problem on how to take the "forward/leading" of the variable
>using SAS. I hereby illustrate my problem with the follwoing example. I
>would very much appreciate it if you could kindly advise me how to achieve
>it (A generic case).
>
>Thank you very much!
>Thomas
>
>*Input;
>ID YEAR X1 X2
>1 1990 0.3 10
>1 1991 0.45 15
>1 1992 0.4 14.5
>1 1993 0.25 15
>1 1994 0.5 20
>3 1999 1.1 50
>3 2000 1.3 100
>3 2001 2.45 150
>3 2002 3.4 145
>3 2003 2.5 75
>3 2004 4.5 90
>
>*Expected output (lag & forward of X1);
>ID YEAR X1 X2 LAG1X1 LAG2X1 LAG3X1 LEAD1X1 LEAD2X1 LEAD3X1
>1 1990 0.3 10 . . . 0.45 0.4 0.25
>1 1991 0.45 15 0.3 . . 0.4 0.25 0.5
>1 1992 0.4 14.5 0.45 0.3 . 0.25 0.5 .
>1 1993 0.25 15 0.4 0.45 0.3 0.5 . .
>1 1994 0.5 20 0.25 0.4 0.45 . . .
>3 1999 1.1 50 . . . 1.3 2.45 3.4
>3 2000 1.3 100 1.1 . . 2.45 3.4 2.5
>3 2001 2.45 150 1.3 1.1 . 3.4 2.5 4.5
>3 2002 3.4 145 2.45 1.3 1.1 2.5 4.5 .
>3 2003 2.5 75 3.4 2.45 1.3 4.5 . .
>3 2004 4.5 90 2.5 3.4 2.45 . . .
>
>/*The output only show for the "lag and lead" of one variable. may I know
>how to generalize to N variable case and the N "lag and lead"?*/
| 
