LAG Function

[newbi]

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

[ballardw]

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.

[newbi]

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

[polingjw]

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.

[newbi]

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 ?

[Ksharp]

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


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


Ksharp

[bharathtuppad]

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;

[Ksharp]

What if the number of Qtr is dynamic ?