When looking at data across consistent units of time (years, quarters, months), there is often interest in creating variables based on how data for a given time period compares to the periods before and after. If you have longitudinal data, you wish to look across units of time within a single subject. When your data is in long form (one observation per time point per subject), this can easily be handled in Stata with standard variable creation steps because of the way in which Stata processes datasets: it stores the entire dataset and can easily refer to any point in the dataset when generating variables. SAS works differently. SAS variables are typically created through a **data** step in which SAS moves through the dataset, observation by observation, carrying out the calculations for the given observation and accessing only one observation at a time. This system of data storage and access makes it possible for SAS to analyze large datasets but also very difficult to create time series variables in SAS using a **data** step. However, **proc expand** provides an easy-to-use alternative to the **data** step.

Let’s start with an example dataset containing only one subject. The dataset below contains US unemployment rates from September, 2006 to August, 2008.

data unemp; input year month rate @@; date = mdy( month, 1 , year ); format date yymm.; datalines; 2006 09 4.5 2006 10 4.4 2006 11 4.5 2006 12 4.4 2007 01 4.6 2007 02 4.5 2007 03 4.4 2007 04 4.5 2007 05 4.5 2007 06 4.6 2007 07 4.7 2007 08 4.7 2007 09 4.7 2007 10 4.8 2007 11 4.7 2007 12 5 2008 01 4.9 2008 02 4.8 2008 03 5.1 2008 04 5 2008 05 5.5 2008 06 5.5 2008 07 5.7 2008 08 6.1 ; proc print data = unemp (obs = 5); run;Obs year month rate date 1 2006 9 4.5 2006M09 2 2006 10 4.4 2006M10 3 2006 11 4.5 2006M11 4 2006 12 4.4 2006M12 5 2007 1 4.6 2007M01

For each month, we wish to know the difference between its rate and the rate of the previous month (r(i) – r(i-1)), its rate and the rate of the next month (r(i+1) – r(i)), and these two differences ((r(i+1)-r(i))-(r(i)-r(i-1)). To do this, we will use **proc expand** to generate a new dataset including these variables. In the **proc expand** line, we will name the new dataset ** unemp_laglead**. We indicate that we do not wish to transform the values (using a spline, for example) but simply to grab the untransformed data from the specified record. We indicate that our time series is defined by **date** in the **id** line and in the three **convert** lines, we create the three values we wish to have for each time point in our data: the rate, the previous rate (**rate_lag1**), and the next rate (**rate_lead1**). In each line, we tell SAS the name of the variable in our new dataset, the type of transformation (**lag**, ** lead**) and the number of time points to look back or ahead for the transformation (1 in this example).

proc expand data=unemp out=unemp_laglead method = none; id date; convert rate = rate_lag1 / transformout=(lag 1); convert rate; convert rate = rate_lead1 / transformout=(lead 1); run;

We can see the resulting dataset.

proc print data = unemp_laglead (obs = 5); run;rate_ rate_ Obs date lag1 rate lead1 year mont 1 2006M09 . 4.5 4.4 2006 9 2 2006M10 4.5 4.4 4.5 2006 10 3 2006M11 4.4 4.5 4.4 2006 11 4 2006M12 4.5 4.4 4.6 2006 12 5 2007M01 4.4 4.6 4.5 2007 1

Based on this dataset, we can now easily calculate the three time series variables we described earlier. But what if we had data for multiple countries? The dataset below contains unemployment data from 2000-2005 for three countries.

data unemp_international; input country $ year rate @@; datalines; US 2000 4 Canada 2000 6.1 UK 2000 5.5 US 2001 4.7 Canada 2001 6.5 UK 2001 5.1 US 2002 5.8 Canada 2002 7 UK 2002 5.2 US 2003 6 Canada 2003 6.9 UK 2003 5 US 2004 5.5 Canada 2004 6.4 UK 2004 4.8 US 2005 5.1 Canada 2005 6 UK 2005 4.9 ; proc print data = unemp_international (obs = 5); run;Obs country year rate 1 US 2000 4.0 2 Canada 2000 6.1 3 UK 2000 5.5 4 US 2001 4.7 5 Canada 2001 6.5

We wish to create lag and lead variables within each country. To do this, we can use **proc expand** with a **by** statement after sorting on country.

proc sort data = unemp_international; by country; run; proc expand data=unemp_international out=unemp_int2 method = none; by country; id year; convert rate = rate_lag1 / transformout=(lag 1); convert rate; convert rate = rate_lead1 / transformout=(lead 1); run; proc print data = unemp_int2; run;rate_ rate_ Obs country year lag1 rate lead1 1 Canada 2000 . 6.1 6.5 2 Canada 2001 6.1 6.5 7.0 3 Canada 2002 6.5 7.0 6.9 4 Canada 2003 7.0 6.9 6.4 5 Canada 2004 6.9 6.4 6.0 6 Canada 2005 6.4 6.0 . 7 UK 2000 . 5.5 5.1 8 UK 2001 5.5 5.1 5.2 9 UK 2002 5.1 5.2 5.0 10 UK 2003 5.2 5.0 4.8 11 UK 2004 5.0 4.8 4.9 12 UK 2005 4.8 4.9 . 13 US 2000 . 4.0 4.7 14 US 2001 4.0 4.7 5.8 15 US 2002 4.7 5.8 6.0 16 US 2003 5.8 6.0 5.5 17 US 2004 6.0 5.5 5.1 18 US 2005 5.5 5.1 .

With **proc expand**, you can also generate moving averages, splines, and interpolated values. For more details, see the **proc expand** pages of the SAS Online Documentation.

## 4 ways to calculate LAG and LEAD in SAS

Looking back some number of periods or rows.

**Lag of first order -**Looking back 1 observation (last value). It is denoted by lag1_value in the image shown below.

**Lag of second order -**Looking back 2 observations. It is denoted by lag2_value in the image shown below.

Calculate Lag in SAS |

**Create a Sample Data**

The program below creates a sample data for demonstration.

data example;

input id value;

cards;

1 25

1 30

1 35

1 40

2 25

2 30

2 37

;

run;

**LAG Function**

In SAS, the

**LAG function**is used to compare the current value to its predecessors. If you want to calculate lag of second order, use

**LAG2 function.**Similarly, you can use

**LAG3 function**for measuring lag of third order.

data temp;

set example;

lag1_value = lag(value);

lag2_value = lag2(value);

run;

**Calculating LAG by ID Variable**

Suppose you are asked to calculate LAG of first order by a grouping variable. In this case, there is no SAS function directly available to accomplish this task. We need to apply some tricks to make it happen.

**Method I : LAG and FIRST.**

The program below uses BY statement and FIRST. to measure lag of first order by ID. First, we are calculating lag and then make the value missing against the first record in a group i.e. ID.

data temp;

set example;

by id;

lag_value = lag(value);

if first.id then lag_value = .;

run;

Lag by Group |

**Method 2 : POINT=, NOBS and _N_**

The POINT= Option is used to point to a specified row number. The _N_ system variable is used to generate a serial number which are used as an index. The NOBS

**option is used to calculate the number of rows in a variable.**

data example;

set example;

lagid = lag(id);

run;

data lags;

if _n_ <= k then do;

set example point = _n_;

if lagid = id then do;

N = _N_ - 1;

set example point = N;

lag_value = value;

end;

end;

else lag_value = .;

set example nobs = k;

by id;

drop lagid;

run;

**Method 3 : PROC SQL and FIRST.**

In the program below, we are first creating a sequence of values and later we are joining two tables taking primary key as current value from the first table with predecessor value from the second table.

data example;

set example;

by id;

if first.id then N = 1;

else N + 1;

run;

proc sql;

select a.* , b.value as lag_value

from example a left join example b

on a.id = b.id and a.N = b.N + 1;

quit;

**Method 4 : PROC EXPAND**

PROC EXPAND is one of the most useful procedure of SAS ETS. It has multiple functions such as creating lag, lead and moving average variables by group(s), aggregation of previous k rows etc.

proc expand data= example out= t2;

convert value = lag_value /transformout = (lag 1);

by id;

run;

**It requires license of SAS ETS package.**

**What is Lead?**

Looking ahead some number of rows or periods.

**Lead Value of First Order -**Looking ahead one observation of a variable (next row).

**Lead Value of Second Order -**Looking ahead two observations of a variable (next to next row).

Lead Value |

**Calculating Lead in SAS**

In SAS, there is no direct function for calculating LEAD. I feel the same there should be a SAS function for it :-) It can be calculated with the following logic :

Create a sequence of numbers and then sort the sequence by descending order. Then we calculate lag of the variable for which we need to calculate lead. At last, we sort the data by sequence ID.

data temp;

set example;

x + 1;

run;

proc sort data = temp;

by descending x;

run;

data temp2;

set temp;

lead_value = lag(value);

run;

proc sort data = temp2;

by x;

run;

**Method 2 : POINT=, _N_ and NOBS**

The NOBS option counts the number of rows in a variable. The _N_+ 1 creates a sequence of numbers start from 2 to (number of records + 1). The POINT= points to a row when the sequence of numbers are less than or equal to number of rows.

data temp;

_N_+1;

if _N_ <= k then do;

set example point=_N_;

lead_value = value;

end;

else lead_value = .;

set example nobs=k;

proc print noobs;

run;

**Calculating Lead by ID Variable**

**Method I : FIRST. and PROC SORT**

In this case, we are applying the same trick that we have used above to calculate lead. The only difference is we need to incorporate grouping variable to calculate lead by a grouping variable (ID).

data temp;

set example;

by id;

if first.id then x =1;

else x + 1;

run;

proc sort data = temp;

by id descending x;

run;

data temp2;

set temp;

by id descending x;

l = lag(x);

lead_val = lag(value);

if first.id then do;

l = .;

lead_val = .;

end;

run;

proc sort data = temp2;

by id x;

run;

Lead Variables |

**Method II : FIRST. and PROC SQL**

In the program below, we are performing self joins on next value with the current value.

data example;

set example;

by id;

if first.id then N = 1;

else N + 1;

run;

proc sql;

select a.* , b.value as lag_value

from example a left join example b

on a.id = b.id and a.N + 1 = b.N;

quit;

**Method III : POINT= and NOBS**

In the program below, we have used the same POINT= and NOBS trick that we have used in the above article.

data example;

set example;

lagid = lag(id);

run;

data leads;

_n_ + 1;

if _n_ <= n then do;

set example point=_n_;

if lagid = id then do;

y = value;

end;

end;

else y = .;

set example nobs = n;

by id;

drop lagid;

proc print noobs;

run;

**Method IV : PROC EXPAND**

PROC EXPAND can be used to calculate leads by a grouping variable. It can also calculate moving averages.

proc expand data= example out= t2;

convert value = lead_value /transformout = (lead 1);

by id;

run;

*The above method works only if you have a license of SAS ETS package.*- Modern kitchen pantry ideas
- Scrap exchange thrift
- Oconto falls public library
- Human hair eyelashes wholesale
- Module 4 lesson 6

> Honestly, the multiple SET stmt approach seems quite

> convoluted, mostly because each SAS variable of

> interest must be protected as a "shadow-named"

> variable through RETAIN and assignments, also the

> file must be read twice.

>

> Scott Barry

> SBBWorks, Inc.

Hello Scott.

True about retaining/assignments, but perfomance wise, it can easily be avoided, with the following modification:

data RESULT;

* AHEAD SET statement starting at OBS=2;

if not _EOF then set SAMPLE (firstobs=2 rename = (A=AA)) end=_EOF;

else AA=.; * last OBS;

set SAMPLE; * normal SET statement starting at OBS=1;

run;

But if I may disagree, not completely true about the efficiency of two pass read.

The two SET are indeed treated by the SAS supervisor as two different tables (double buffer, double pointer), but let us remember that when processing files most of the effort is done at disk I/O. And because of that, at the lowest level every I/O operation is done by block and cached into memory. So there is a very good chance, that for the same file, the second read will not produce another read operation and the data will be retrieved from memory.

Then of course, as already pointed by kmg, PROC SORT falls in the group of the most resource consuming procedures.

By the way, it just occurred to me another approach, which would be, to perform a merge with the same table (WITHOUT the BY statement) being one of them one observation "ahead" of the other.

data RESULT;

merge SAMPLE SAMPLE (firstobs=2 keep=A rename=(A=AA));

run;

Indeed, so many ways.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.

Then she got off, got on all fours, so that the first guy had to kneel down to enter her from behind. And she herself sucks the brown hairy cock. The first one fries her from behind.

## Opposite of lag sas

Hi, Sergunchik, we haven't seen each other for a hundred years. He handed me a travel bag and hugged his friend around the waist, and then returned her friendly kiss. The tension of waiting for the meeting was slightly released, and I already quite calmly put the bag on the nightstand, and then turned to my husband with a smile.

SAS Bootcamp 3.3 - Reading across rows - LAG functionAnd I can see that you are afraid. He gently touched my cheek with his hand. I ran along it further, behind the ear.

### You will also like:

- Hamilton county busted newspaper
- The universe season 8
- Lg straight talk phone
- 5e low level monsters
- Red hat coffee hour
- Lasc fax filing numbers
- Nail gun hanger hook
- 2019 audi q3 reliability
- Hyde beach house airbnb
- U haul northside
- Wash out crossword clue
- Garland isd open enrollment

Him to open it. At dinner, Anna Viktorovna asked him about life, work, asked about the girl, to which Andrei was a little embarrassed and said. That the girl was not there. The hostess of the house told a little about herself and why she divorced her husband. The bottle of wine began to end and the conversation turned a little into the topic of relationships and sex.

**476**477 478