Dynamic and conditional distinct counting in SAS VA

[tjalleph]

Hi all,

New to SAS and SAS VA, coming from other BI tools. In SAS VA I'm trying to do something that could be called dynamic and conditional distinct couting. 

A simplified sample of my data would like this:

ID	Category	Type	Coded_answer
1	A	X	0
1	B	X	0
1	C	X	1
1	D	X	0
1	E	X	0
2	A	Y	0
2	C	Y	0
2	D	Y	0
3	A	X	0
3	B	X	0
3	C	X
3	D	X	2
4	A	X	1
4	B	X	0
4	C	X	0

Then I'd want to a distinct couting of ID's, categorizing them on the basis of the maximum of Coded_answer in that group. 

It would need to do so for many different slices of the data. Resulting tables for this dataset would have to look like:

Resulting tables
	max = 0	max = 1	max = 2
Total	1	2	1
Categories	max = 0	max = 1	max = 2
Cat A	3	1	0
Cat B	3	0	0
Cat C	2	1	0
Cat D	2	0	1
Cat E	1	0	0
Type	max = 0	max = 1	max = 2
X	1	1	1
Y	1	0	0

What would we be way to achieve this result in SAS VA?

Kind regards,

Tjalle

[FredrikE]

Hi!

one way is to create a new variable for each code and then (on the variables properties); set the aggregation to max.

Variable Max0:

IF coded_answer = 0 return 0 

ELSE .

Variable Max1:

IF coded_answer = 1 return 1 

ELSE .

Variable Max(n):

IF coded_answer = n return n 

ELSE .

//Fredrik

[tjalleph]

Hi FredrikE, 

Thanks for taking the time to respond. I haven't tried your solution yet, I will when I get back to SAS VA.

However, I do not see yet how this is giving me a distinct count of the IDs. Could you explain me how that would work in this code?

Kind regards,

Tjalle

[FredrikE]

Hi!

Maybe I was a bit too fast here, not understanding your logic fully 🙂

For the first table, why is the value for 0='1' and not '4'?

Resulting tables
	max = 0	max = 1	max = 2
Total	1	2	1

If so one way is to create the new variable (0,1,2...n) by assigning the ID to them if the coded_answer is correct and then set the aggregation so distinc count.

Variable unique0:

IF coded_answer = 0 return ID 

ELSE .

Variable unique1:

IF coded_answer = 1 return ID 

ELSE .

Variable unique(n):

IF coded_answer = n return ID 

ELSE .

//Fredrik

[tjalleph]

Hi Fredrik,

I think we're getting closer to me asking the correct question 🙂

Answer to your question:

For the first table, why is the value for 0='1' and not '4'?

Resulting tables
	max = 0	max = 1	max = 2
Total	1	2	1

The first value at max=0 is 1, because for only one ID the max(coded_answers)=0 over all selected rows. Therefore, the values in this table are a count, and do not refer to a specific ID. The other resulting tables are also counts of distinct ID. Cat A is in all 4 IDs and therefore there are 4 counts, but Cat E is only in one ID, and therefore there is only 1 count. 

I'm looking for the way to first aggregate to the max per ID, and then distinct count the IDs per categorized max (in this example, the values being 0, 1 or 2). It should do so dynamically, based on the grouping/slicing at hand.

Regards,

Tjalle

[FredrikE]

Hi!

Been of for a week...:)

If you are using SAS VA8.3 you can create an aggregated table with your max and then use this table as input for further calculations.

Don't know if this helps you, but i don't think you can do everything by just adding calculations and not the aggregated table.

Havn't tested any more, but maybe this can help you somehow 🙂

//Fredrik