DATA Step, Macro, Functions and more

Column showing balance off a constant value

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

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.

 

Many thanks in advance!

 

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;

View solution in original post


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;
Trusted Advisor
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 83 views
  • 0 likes
  • 5 in conversation