CODEWITHSUNDEEP
sasproc-sql
LdM
LdM

Reputation: 704

Count amount within an year

I have a dataset which looks like

ID  STATUS  YEAR   AMOUNT  DT_1
.    OPEN   2010   12      12
.    OPEN   2009   24      10
.    OPEN   2008   32      1
AA   CLOSE  2015   150     12
AA   CLOSE  2014   200     10
AA   CLOSE  2010   10      8
AA   CLOSE  2009   20      7
AA   CLOSE  2008   18      5
AA   OPEN  2012   21      8
AA   OPEN  2001   20      7
AA   OPEN  2000   18      5

Column DT_1 may take from a max of 12 to a min of 1. I would like to calculate how much amount there is within this range each time. This means that I should assign to the current year the previous amount. I would like to expect something like this

   ID  STATUS  YEAR   AMOUNT  DT_1
    .    OPEN   2010   12      24
    .    OPEN   2009   24      32
    .    OPEN   2008   32      .
    AA   CLOSE  2015   150     200
    AA   CLOSE  2014   200     10
    AA   CLOSE  2010   10      20
    AA   CLOSE  2009   20      18
    AA   CLOSE  2008   18      .
    AA   OPEN  2012   21      20
    AA   OPEN  2001   20      18
    AA   OPEN  2000   18      .

I have tried as follows

proc sql;
create table tab1 as
select ID, status, year, sum(amount) as tot_amount, dt_1 
from tab
group by 1,2,3;
quit;

but it does not give me the expected output.

EDIT: I had to edit the question as the expected output was different.

Upvotes: 0

Views: 35

Answers (2)

Tom
Tom

Reputation: 51566

So DT_1 is the amount form the previous year? If so it would be a lot easier if the data was sorted by increasing value of YEAR, instead of decreasing as displayed in the question. Then you can just use the LAG() function.

proc sort data=HAVE out=WANT ;
  by id status year ;
run;
data WANT;
  set want ;
  by id status year;
  dt_1 = lag(amount);
  if first.status then dt_1=.;
run;

Upvotes: 1

PeterClemmensen
PeterClemmensen

Reputation: 4937

See if this is what you want

data have;
input ID $ STATUS $ YEAR AMOUNT;
datalines;
.  OPEN  2010 12 
.  OPEN  2009 24 
.  OPEN  2008 32 
AA CLOSE 2015 150
AA CLOSE 2014 200
AA CLOSE 2010 10 
AA CLOSE 2009 20 
AA CLOSE 2008 18 
AA OPEN  2012 21 
AA OPEN  2001 20 
AA OPEN  2000 18 
;

data want(drop = s);
   merge have
         have(firstobs = 2 keep = amount STATUS 
              rename = (amount = DT_1 STATUS = s));
   if STATUS ne s then DT_1 = .;
run;

Upvotes: 1

Related Questions