topic Re: Column showing balance off a constant value in SAS Programming

Column showing balance off a constant value

Max503 — Wed, 13 Jun 2018 16:01:46 GMT

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

Re: Column showing balance off a constant value

Kurt_Bremser — Wed, 13 Jun 2018 16:08:12 GMT

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

Re: Column showing balance off a constant value

novinosrin — Wed, 13 Jun 2018 16:13:45 GMT

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;

Re: Column showing balance off a constant value

mkeintz — Wed, 13 Jun 2018 16:14:12 GMT

What have you tried so far?

Re: Column showing balance off a constant value

Ksharp — Thu, 14 Jun 2018 13:50:37 GMT

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;

Re: Column showing balance off a constant value

Max503 — Thu, 14 Jun 2018 14:09:35 GMT

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

Re: Column showing balance off a constant value

Max503 — Thu, 14 Jun 2018 15:07:48 GMT

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

Thanks again.