Column showing balance off a constant value

[Max503]

Hello,

I have a dataset that looks this way:

Year_Month   Total Unit   Sold  

201701            100             10

201702            100             15

201703            100             18

201704            100             21

etc.

Now I need  to create a new attribute (Named 'Remaining') showing balance off a constant value (Total Unit) until all units are sold.

Year_Month   Total Unit   Sold   Remaining

201701            100             10        90

201702            100             15        75

201703            100             18        57

201704            100             21        36

etc.

Any help would be greatly appreciated. Either Proc Sql or Data step is fine.

Many thanks in advance!

Max

[Kurt_Bremser]

Data step: retain remaining. At first.xxxx of whatever grouping you use, set it to total_unit - sold, otherwise just subtract sold from it.

[novinosrin · Accepted solution]

data have;
input Year_Month $   TotalUnit   Sold  ;
cards;
201701            100             10
201702            100             15
201703            100             18
201704            100             21
;

data want;
set have;
retain remaining;
if _n_=1 then remaining=TotalUnit-sold;
else remaining=remaining-sold;
run;

[mkeintz]

What have you tried so far?

[Ksharp]

data have;
input Year_Month $   TotalUnit   Sold  ;
cards;
201701            100             10
201702            100             15
201703            100             18
201704            100             21
;

data want; 
 set have;
 sum+sold;
 balance=totalunit-sum;
run;

[Max503]

Thanks very much! Both codes work fine.

I would also appreciate if someone can provide a SQL code as I need to run this in DB2 as well.

Thanks again.

Max

[Max503]

I figured out the DB2 SQL code myself using the logic mentioned here.

Thanks again.