Hi Experts,
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
Week-1
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.
thanks
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.
Hello @gyambqt,
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.
Best regards,
Petri
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.
Yes, definitely.
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 😃
Best regards,
Petri
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.