I am looking for a way to display the distinct count of users based on the user type, in a cross tab, based on grouping displayed as a hierarchy is expanded.
A user could be one of two types - and there needs to be a column for each type, for the distinct count of users of that type. Say, Type A, and Type B.
Each user has a unique identifier and a type recorded, and can have multiple rows of data for each level of the hierarchy.
The hierarchy in the cross tab, is: Financial Year -> Financial Year Quarter -> Residential State
When the cross tab displays Financial Year, it is to display distinct count in columns for Type A, and Type B, for the Financial Year.
Then, when hierarchy expands to Financial Year Quarter, it displays distinct count of Type A and Type B, for the Financial Year -> Financial Year Quarter.
Then, when hierarchy expands to Residential State, it displays distinct count of Type A and Type B, for the Financial Year -> Financial Year Quarter -> Residential State.
You should be able to do this by creating a distinct count on your unique identifier. To create a distinct count, right click on your unique identifier and select New Calculation. Select Distinct Count for your type to create a new aggregated measure. You can then put your hierarchy on rows, the Type data item on columns, and select your new distinct count as the measure. A screenshot using sample employee data is below, but this should give you an idea of what it would look like.
Is this possible? Sounds like it should be simple, but I haven't worked it out yet.
You should be able to do this by creating a distinct count on your unique identifier. To create a distinct count, right click on your unique identifier and select New Calculation. Select Distinct Count for your type to create a new aggregated measure. You can then put your hierarchy on rows, the Type data item on columns, and select your new distinct count as the measure. A screenshot using sample employee data is below, but this should give you an idea of what it would look like.
Thank you @TSBruce I am able to display distinct user count based on hierarchy as per your guidance.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.