topic Re: Counting unique values by subsets of a variable in SAS Programming

Counting unique values by subsets of a variable

AJChamberlain — Tue, 05 Feb 2019 15:29:04 GMT

Greetings community

I am trying to work out a way to sum the number of unique values that occur for a variable in the dataset. So for example if the values for my observations were:

The answer would be 5 because there are 5 unique values. But I want to find this answer for each combination of two other variables in my dataset (type and version), so my data looks like this, where I am trying to count the number of unique instances of 'value':

Value	Type	Version
1	1	A
1	1	A
2	1	A
2	1	A
3	1	A
4	1	A
6	1	A
1	2	A
1	2	A
3	2	A
3	2	A
1	1	B
3	1	B
4	1	B
5	1	B
1	2	B
2	2	B
3	2	B
4	2	B
5	2	B
6	2	B

and my answer needs to look something like this:

	Version
Type	A	B
1	5	4
2	2	6

I think I can do this with Proc SQL but it has eluded me so far, any suggestions gratefully received!

Thanks

Andy

Re: Counting unique values by subsets of a variable

PaigeMiller — Tue, 05 Feb 2019 15:35:59 GMT

data a;
	input value type version $;
	cards;
1	1	A
1	1	A
2	1	A
2	1	A
3	1	A
4	1	A
6	1	A
1	2	A
1	2	A
3	2	A
3	2	A
1	1	B
3	1	B
4	1	B
5	1	B
1	2	B
2	2	B
3	2	B
4	2	B
5	2	B
6	2	B
;
run;

proc sql;
	select type,version,count(distinct value) from a group by type,version;
quit;

Now this does the calculations of the number of distinct levels of value, it does not re-arrange the results into a table with two rows (one for each type) like you show, but that's pretty simple to do in PROC REPORT if you really have to have it in that form.

Re: Counting unique values by subsets of a variable

novinosrin — Tue, 05 Feb 2019 15:58:37 GMT

Your sample output doesn't look like a dataset rather report with proc report/tabulate

Re: Counting unique values by subsets of a variable

AJChamberlain — Tue, 05 Feb 2019 17:13:25 GMT

Thakns Paige, that's worked for me. A