topic Re: Calculate distinct observations in all population in SAS Programming

Calculate distinct observations in all population

Jedrzej — Wed, 17 Feb 2021 11:28:04 GMT

Hi, i have a dataset;

ID	VAR
1	A
1	B
2	B
3	A
3	C
4

and I want to calculate the percentege of distinct value in var a for all distinct ID's:

for example: population is 4 (distinct ID), occurance of var "B" is 2 so the result is 50%

thank you

example output:

so as I wrote earlier the entire population=distinct ID, no matter if there are blanks in var. The output is to show percentage of occurance of var in the entire population, like this:

VAR	Percentage of occurance var/ entire poplulation (distinct ID)
A	50%
B	50%
C	25%

Re: Calculate distinct observations in all population

PeterClemmensen — Wed, 17 Feb 2021 09:59:01 GMT

What do you mean by 'Population is 4' ?

What does your desired result look like given this data?

Re: Calculate distinct observations in all population

Jedrzej — Wed, 17 Feb 2021 10:03:24 GMT

distinct ID

Re: Calculate distinct observations in all population

ballardw — Wed, 17 Feb 2021 10:20:36 GMT

@Jedrzej wrote:
distinct ID

Since your "example" data has exactly zero occurrences of B for Id=4 I suggest that you provide an actual example of data where the B would actually be "50%". Better would be to provide expected output for ALL of the "example" data so we know what you expect us to do with missing values of Var.

Re: Calculate distinct observations in all population

andreas_lds — Wed, 17 Feb 2021 10:23:08 GMT

@Jedrzej wrote:

Hi, i have a dataset;

ID VAR

1 A

1 B

2 B

3 A

3

4 A

and I want to calculate the percentege of distinct value in var a for all ID:

for example: population is 4, occurance of var "B" is 2 so the result is 50%

thank you

No, this does not make any sense at all.

You have six obs in the table, so the two B are 33% or 40% if the obs with missing VAR should be ignored.

So, if you want help, please show what you expect as result and explain the logic you want applied.

Re: Calculate distinct observations in all population

Jedrzej — Wed, 17 Feb 2021 10:34:19 GMT

No, I have 6 obs, but distinct ID's are 4 so the entire population is 4, one ID= one item. So there are two B, one for ID1 second for ID2. The question is: how many B are there for the entire population?

Re: Calculate distinct observations in all population

ballardw — Wed, 17 Feb 2021 10:48:14 GMT

As I suggested previously show the entrie expected output for your example data.

like what is the result for A?

As phrased there are exactly 3 "distinct" values of Var: A, B and blank.

And maybe a larger completely worked example as your logic isn't really clear.

Re: Calculate distinct observations in all population

Jedrzej — Wed, 17 Feb 2021 11:02:51 GMT

ok, so as I wrote earlier the entire population=distinct ID, no matter if there are blanks in var. The output is to show percentage of occurance of var in the entire population, like this:

VAR	Percentage of occurance var/ entire poplulation (distinct ID)
A	50%
B	50%
C	25%

Re: Calculate distinct observations in all population

Ksharp — Wed, 17 Feb 2021 11:50:18 GMT

data have;
input ID	VAR $;
cards;
1	A
1	B
2	B
3	A
3	C
4	 .
;
proc sql;
create table want as
select var,count(distinct id)/(select count(distinct id) from have) as per format=percent8.2
 from have 
  where var is not missing
  group by var;
quit;