Help using Base SAS procedures

YTD Calculation

Reply
N/A
Posts: 0

YTD Calculation

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
Super Contributor
Super Contributor
Posts: 3,174

Re: YTD Calculation

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.
N/A
Posts: 0

Re: YTD Calculation

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
Super Contributor
Super Contributor
Posts: 3,174

Re: YTD Calculation

What you have suggested sounds reasonable.

Scott Barry
SBBWorks, Inc.
Ask a Question
Discussion stats
  • 3 replies
  • 760 views
  • 0 likes
  • 2 in conversation