BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Max503
Calcite | Level 5

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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

6 REPLIES 6
novinosrin
Tourmaline | Level 20
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
PROC Star

What have you tried so far?

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Ksharp
Super User
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
Calcite | Level 5

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
Calcite | Level 5

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

 

Thanks again.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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