Assume I have the following data and want to create cross table in VA.
Week number(date). Age group. Number of people
1 1-30 1
1 31-60 2
1 61 and above 3
2 31-60 1
2 61 and above 2
My new calculated item will be relative function in period with -1 on week number for number of people
so you have
Week number(date). Age group. Number of people. Week-1(calculated)
1 1-30 1 .
1 31-60 2 .
1 61 and above 3 .
2 31-60 1 2( from week1)
2 61 and above 2 3 (from week1)
now i I want to create a cross table in VA 8.3 with filter week=2
the row variable is age group and column variable is week-1 I would get:
Filter is week=2
age 31-60 2
age 61 and above. 3
age 1-30 is missing from this cross table as this category does not exist in week2. But I want to see it here as the column I used is week-1. How do I get this missing category displayed in the cross table without manipulating the source data.
I did not actually get why do you want to have 1-30 age group in filtered data when you already applied filter = week 2.
However if you want to achieve the desired you will have to use multi-conditional filter eg: OR , AND. you can use Boolean operator for the same.
I agree with using multi-conditional rules. I did a little experimenting.
Drop down list populates My Week parameter.
IF ( 'My Week'p NotMissing ) RETURN ( ( TreatAs(_Number_, 'My Week'p) = ( 'Week Num'n + 1 ) ) OR ( TreatAs(_Number_, 'My Week'p) = TreatAs(_Number_, 'Week Num'n) ) ) ELSE ( 1 = 1 )
This seemed to work as you described.
Thanks for your prompt reply.
Could you show me your screenshots for other processes?
1. How you define l my week parameter
2. role of cross table
3. detailed of week filter?
5.How you have created people (week-1)? I used relative function with -1 in week
the week here is a date variable and it seems in your testing data you have used a numeric value for that.
IF ( 'My Week'p NotMissing ) RETURN ( ( TreatAs(_Number_, 'My Week'p) = ( 'Week Num'n + 1 ) ) OR ( TreatAs(_Number_, 'My Week'p) = TreatAs(_Number_, 'Week Num'n) ) ) ELSE ( 1 = 1 )The OK button is disabled (grey). Click somewhere on the window's canvas to make the OK button activate (blue) and click OK.
I hope this helps 😃
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.
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.