Date: Sat, 18 Oct 2008 17:40:12 -0400
Reply-To: Randy <randistan69@HOTMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Randy <randistan69@HOTMAIL.COM>
Subject: Re: Cumulative Summing
A slight variation of the same problem
Date Buyer_ID Seller_ID Amount
Jan3 1 2 500
Jan4 2 3 400
Jan5 1 3 100
Jan6 2 1 300
I want to construct a Cum_Sum
where
Date ID Cum_Sum
Jan3 1 500
Jan3 2 -500
Jan4 2 -100
Jan4 3 -400
Jan5 1 600
Jan5 3 -500
Jan6 1 300
Jan6 2 200
and so on...
I need help.
Randy
On Sat, 18 Oct 2008 08:51:43 -0400, Nat Wooding
<Nathaniel.Wooding@DOM.COM> wrote:
>Randy
>
>The following does what Karma suggested
>
>Data Randy;
>input DATE $4. ID @16 BOUGHT 3. @27 SOLD 3.;
>/*
>123456789112345678921234567893
>Date ID Bought Sold
>*/
>cards;
>Jan3 1 500
>Jan4 2 400
>Jan5 1 100
>Jan6 1 300
>Jan7 3 400
>Jan8 2 300
>PROC PRINT;RUN;
>
>Proc Sort ;
> by id ;
>run;
>
>Data Randy;
> set randy;
> by id;
> if first.id then cumsum=0;
> if bought ne . then cumsum + bought;
> else cumsum + -sold;
>run;
>Proc sort;
> by date;
>run;
>
>Proc Print;
>run;
>
>
>Do note that I avoided the issue of the missing values by testing to see
if
>a value of bought was missing . If you should have an instance where both
>values were missing, then the cumsum would be set to missing for that Id
at
>that point.
>
>This final data will be sorted properly as long as your sample data remain
>simple. In a real world situation, you would want real dates meaning
month,
>day and year or julian dates and not the mmmdd format that you show.
>
>You could set the missing values to 0 but it is not necessary as I show.
>
>Note that in SAS if you write cumsum + bought , the resultant value of
>Cumsum is automatically retained and carried to the next observation.
>
>HTH
>
>Nat Wooding
>Environmental Specialist III
>Dominion, Environmental Biology
>4111 Castlewood Rd
>Richmond, VA 23234
>Phone:804-271-5313, Fax: 804-271-2977
>
>
>
> Randy
> <randistan69@HOTM
> AIL.COM> To
> Sent by: "SAS(r) SAS-L@LISTSERV.UGA.EDU
> Discussion" cc
> <SAS-L@LISTSERV.U
> GA.EDU> Subject
> Cumulative Summing
>
> 10/18/2008 02:24
> AM
>
>
> Please respond to
> Randy
> <randistan69@HOTM
> AIL.COM>
>
>
>
>
>
>
>Dear All:
> Here is my problem. My Data set is as follows:
>Date ID Bought Sold
>Jan3 1 500
>Jan4 2 400
>Jan5 1 100
>Jan6 1 300
>Jan7 3 400
>Jan8 2 300
>
>I want to construct another Variable (Buy-Sell), which is the cumulative
>sum of the Units held by each individual ID
>
>Date ID Buy Sell Cum_Sum
>Jan3 1 500 500
>Jan4 2 400 -400
>Jan5 1 100 600
>Jan6 1 300 300
>Jan7 3 400 -400
>Jan8 2 300 -100
>Any suggestions?
>
>
>CONFIDENTIALITY NOTICE: This electronic message contains
>information which may be legally confidential and/or privileged and
>does not in any case represent a firm ENERGY COMMODITY bid or offer
>relating thereto which binds the sender without an additional
>express written confirmation to that effect. The information is
>intended solely for the individual or entity named above and access
>by anyone else is unauthorized. If you are not the intended
>recipient, any disclosure, copying, distribution, or use of the
>contents of this information is prohibited and may be unlawful. If
>you have received this electronic transmission in error, please
>reply immediately to the sender that you have received the message
>in error, and delete it. Thank you.
| 
