Site hosted by Build your free website today!
SAS(r) Software Tips
How to perform a LAG in SQL 
There are two tricks to this. One is making a view that contains a record counter and using that as the input. The second is doing a reflexive left join that uses the record counter in its ON clause. 
data <viewname>/ view=<viewname>; 
set <viewname>; 
counter = _n_ ; 
run ; 

proc sql ; 
create table oflags as 
select a.*,b.<fieldname> as lagged 
from <viewname> a LEFT JOIN <viewname> b 
on b.counter = a.counter - 1 ; 
quit ; 

Start by creating a view of the input data step and including a counter field thats identifies each row of the dataset 

In the SQL, join the table to itself. In this example, we keep records where the dataset is joined to the prior record. The prior record contributes the lagged value.

Extensions to this include changing the -1 to any number to get a lag further back than the previous record, and adding a key match to the ON condition to get a lag within groups of keyed records.  

Contact Don Stanley