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
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;
Data step: retain remaining. At first.xxxx of whatever grouping you use, set it to total_unit - sold, otherwise just subtract sold from it.
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;
What have you tried so far?
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;
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
I figured out the DB2 SQL code myself using the logic mentioned here.
Thanks again.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.