## Column showing balance off a constant value

Solved
Occasional Contributor
Posts: 7

# Column showing balance off a constant value

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.

Max

Accepted Solutions
Solution
Thursday
PROC Star
Posts: 1,558

## Re: Column showing balance off a constant value

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;

All Replies
Super User
Posts: 9,873

## Re: Column showing balance off a constant value

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
Thursday
PROC Star
Posts: 1,558

## Re: Column showing balance off a constant value

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;
Posts: 1,309

## Re: Column showing balance off a constant value

What have you tried so far?

Super User
Posts: 10,682

## Re: Column showing balance off a constant value

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;
Occasional Contributor
Posts: 7

## Re: Column showing balance off a constant value

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

Occasional Contributor
Posts: 7

## Re: Column showing balance off a constant value

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

Thanks again.

☑ This topic is solved.