BookmarkSubscribeRSS Feed
Calcite | Level 5



We have a cross table that can be drilled down and it has the Sales value for the month and the Actual YTD for the monthly cumulative. However, in Sept there was no Sales, and so no entry for that month. So it’s not showing an Actual YTD for that month either then, in other words September doesn't appear in the table.


Formula for Actual YTD (€) is   CumulativePeriod(_Sum_, Sales,_IgnoreAllTimeFrameFilters_, 'Date'n, _Inferred_, _ByYear_, 0, 1,_Full_, {Date})


Can we adjust the formula to force an entry for September even is there is no Sales data for that month?








Jade | Level 19

This is a data organization and recognition task.  I don't think it will be resolved by a modification of the formula you present.


If, in your data, you can generate a dummy record with sales=0 for missing months, probably the sas coding you are using would work.


But, since you have not provided the code, nor representative data in the form of a sample data step, we can't offer advice in the form of sas program statements.  I think you will need to help us help you.



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

Pyrite | Level 9

Hello @A_Darcy ,


What version are you using? I tried to recreate your issue with VA8.5 and below is the Crosstab I got. Is this different from yours? In your source data does the Date variable still have September even though Sales doesn't have value in it?




Actual YTD (€) = CumulativePeriod(_Sum_, 'Sales_missing'n,
_IgnoreAllTimeFrameFilters_, 'Date'n, _Inferred_, _ByYear_, 0, 1,
_Full_, {Date})


I have attached the data I used.


Best regards,




Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 1 like
  • 3 in conversation