turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Visual Analytics
- /
- A Distinct Count where a character variable equals...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-18-2015 01:17 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jplarios

06-22-2015 10:43 AM

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?

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jplarios

06-19-2015 05:16 AM

Hi,

First you can't equal charter variable to measure.

So can please explain more to better understand.

Thanks & Regards,

Teja Surapaneni.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to TejaSurapaneni

06-19-2015 05:37 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jplarios

06-22-2015 10:43 AM

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?

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Sam_SAS

06-23-2015 09:17 AM

Sam,

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