BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
3 REPLIES 3
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
deleted_user
Not applicable
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
What you have suggested sounds reasonable.

Scott Barry
SBBWorks, Inc.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 2480 views
  • 0 likes
  • 2 in conversation