BookmarkSubscribeRSS Feed
newbi
SAS Employee
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
7 REPLIES 7
ballardw
Super User
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
SAS Employee
Thanks for the resonse. I actully need to create the "Previour YrQtr Sales" column.
polingjw
Quartz | Level 8
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
SAS Employee
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
Super User
Hi.
I think you need firstly to sort your dataset.Such as:


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


Ksharp
bharathtuppad
Obsidian | Level 7


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
Super User

What if the number of Qtr is dynamic ?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1200 views
  • 0 likes
  • 5 in conversation