Exploring, modeling, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

A Distinct Count where a character variable equals a flag?

Accepted Solution Solved
Reply
Regular Contributor
Posts: 163
Accepted Solution

A Distinct Count where a character variable equals a flag?

Hello,

Question, I would like to create to measures on a trend line where each of 2 measures are each defined by a flag where something like,  Distinct Count where a category variable equals a certain flag. Is this possible in SAS VA 7.2?

Thank you for any input.


Accepted Solutions
Solution
‎06-22-2015 10:43 AM
SAS Super FREQ
Posts: 286

Re: A Distinct Count where a character variable equals a flag?

Hello,

Distinct count is an aggregated operator, and I don't think you can evaluate the value of a character (or a numeric) flag on a row-by-row basis as part of an aggregated expression.

One workaround would be to create two calculated measures similar to the following:

ObsoleteUnitAge

IF ( 'Age Status'n In ('Obsolete') )

RETURN 'Unit Age'n

ELSE .

and

CurrentUnitAge

IF ( 'Age Status'n In ('Current') )

RETURN 'Unit Age'n

ELSE .

You could then calculate the distinct count for these calculated columns to get the distinct count of 'Unit Age' where the 'Age Status' flag is "Obsolete" and also the distinct count where the flag is "Current".

Is that the sort of result that you are looking for?

View solution in original post


All Replies
Regular Contributor
Posts: 196

Re: A Distinct Count where a character variable equals a flag?

Hi,

First you can't equal charter variable to measure.

So can please explain more to better understand.

Thanks & Regards,

Teja Surapaneni.

Regular Contributor
Posts: 163

Re: A Distinct Count where a character variable equals a flag?

Hi Teja,

For example, I have a trend that sums up 'if' a certain flag is set - so measure 1 is  sum if flag = 'y'  and measure 2 is sum if flag = 'x'.

How can I do this but with distinct count?

Solution
‎06-22-2015 10:43 AM
SAS Super FREQ
Posts: 286

Re: A Distinct Count where a character variable equals a flag?

Hello,

Distinct count is an aggregated operator, and I don't think you can evaluate the value of a character (or a numeric) flag on a row-by-row basis as part of an aggregated expression.

One workaround would be to create two calculated measures similar to the following:

ObsoleteUnitAge

IF ( 'Age Status'n In ('Obsolete') )

RETURN 'Unit Age'n

ELSE .

and

CurrentUnitAge

IF ( 'Age Status'n In ('Current') )

RETURN 'Unit Age'n

ELSE .

You could then calculate the distinct count for these calculated columns to get the distinct count of 'Unit Age' where the 'Age Status' flag is "Obsolete" and also the distinct count where the flag is "Current".

Is that the sort of result that you are looking for?

Regular Contributor
Posts: 163

Re: A Distinct Count where a character variable equals a flag?

Sam,

Thank you : so create a calculated then distinct - excellent. I applied and worked nice.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 616 views
  • 2 likes
  • 3 in conversation