topic LAG Function in SAS Programming

LAG Function

newbi — Fri, 18 Mar 2011 16:19:13 GMT

Good Afternoon;

I have table with following columns:

Yr qtr rep sales
2010 1 100 $1000
2010 2 100 $2000

I would like to create datastep to add previous yrqtr sales column using LAG function. So i would have

Yr qtr rep sales previousyrqtrsales
2010 1 100 $1000 $0
2010 2 100 $2000 $0
2011 1 100 $3000 $1000
2011 2 100 $4000 $2000

Data Work.Sales (keep=year qtr rep sales PreviousYrQtr_Sales);
Set work.Sales_new;
Format PreviousYrQtr_Sales 8.;
PreviousYrQtr_Sales = LAG(sales);
Run;

This gave me the value from the previous row in previousYrQtr_sales column

Yr qtr rep sales previousyrqtrsales
2010 1 100 $1000 $0
2010 2 100 $2000 $1000
2011 1 100 $3000 $2000
2011 2 100 $4000 $3000

What function or code i need to get previous year qtr sales listed ?

Thanks

Re: LAG Function

ballardw — Fri, 18 Mar 2011 16:47:19 GMT

Are you lookin for PROC PRINT perhaps;

Proc print data=;
var Yr qtr rep sales previousyrqtr_sales;
run;

Depending on how your dollar signs are getting displayed you probably want to use a DOLLARw.d format.

The first value will be missing for previousyrqtr_sales as there isn't a previous one to read.

Re: LAG Function

newbi — Fri, 18 Mar 2011 16:51:00 GMT

Thanks for the resonse. I actully need to create the "Previour YrQtr Sales" column.

Re: LAG Function

polingjw — Fri, 18 Mar 2011 16:51:23 GMT

Will this work?

[pre]
data sales;
input year qtr rep sales:dollar4.;
datalines;
2010 1 100 $1000
2010 2 100 $2000
2011 1 100 $3000
2011 2 100 $4000
;
run;

data sales;
if _n_=1 then do while(not eof);
set sales end=eof;
array sales_array[2010:2011, 1:2] _temporary_;
sales_array[year, qtr]=sales;
end;
set sales;
retain sales_array;
if year > 2010 then previousyrqtrsales = sales_array[year-1, qtr];
else previousyrqtrsales = 0;
run;
[/pre]

Alternatively, you could sort by qtr and year and then use the lag function.

Re: LAG Function

newbi — Fri, 18 Mar 2011 19:09:22 GMT

Thanks for all your help. I was able to use Array function.

One more question. With in the array function how can i create additional column that would give me a "Yearly" total for previous year ?

Re: LAG Function

Ksharp — Sat, 19 Mar 2011 03:42:54 GMT

Hi.
I think you need firstly to sort your dataset.Such as:

[pre]
proc sort data=have;
by qtr yr;
run;
[/pre]

Ksharp

Re: LAG Function

bharathtuppad — Fri, 08 May 2015 12:07:52 GMT

data have;
input Yr $       qtr $        rep $           sales $;
pre_sales=lag2(sales);
cards;
2010    1          100            $1000
2010    2          100            $2000
2011    1          100            $3000
2011    2          100            $4000
;
proc print;
run;

Re: LAG Function

Ksharp — Fri, 08 May 2015 13:20:20 GMT

What if the number of Qtr is dynamic ?