topic Re: YTD Calculation in SAS Procedures

YTD Calculation

deleted_user — Wed, 04 Aug 2010 14:39:45 GMT

I am looking for the YTD calculation and for a particular id, if there is no quantity in that month but there are quantities in previous months, it need to be '0' for that month and Year to date (sum) for that month. Thanks in advance for your suggestions

Input file:
data test;
input id $ date monyy7. quantity;
infile cards;
A Jan2010 12
A Feb2010 13
B Feb2010 15
C Jan2010 10

Output required is:

Date Id Quantity YTD_Quantity
Jan2010 A 12 12
Jan2010 C 10 10
Feb2010 A 13 25
Feb2010 B 15 15
Feb2010 C 0 10
;
Thanks Message was edited by: nash

Re: YTD Calculation

sbb — Wed, 04 Aug 2010 16:18:06 GMT

Create a SAS file containing only unique DATE variable values (PROC SORT NODUPKEY), then MERGE (with BY DATE) the new file back onto your WORK.TEST file, giving you one observation for each ID/DATE combination, some obs having a "SAS missing" value for QUANTITY.

Then look at using PROC SUMMARY to generate your cumulative SUM metric.

Scott Barry
SBBWorks, Inc.

Re: YTD Calculation

deleted_user — Wed, 04 Aug 2010 16:54:46 GMT

Scott,

Thanks for your suggestion. In the example, I mentioned only two months but real data has 12 months and it will be 12 small datasets and merging will be with those 12 files. Is there a better way to this ?

Thanks

Re: YTD Calculation

sbb — Wed, 04 Aug 2010 17:34:43 GMT

What you have suggested sounds reasonable.

Scott Barry
SBBWorks, Inc.