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:
1
1
2
2
3
4
6
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
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.
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.
Thakns Paige, that's worked for me. A
Your sample output doesn't look like a dataset rather report with proc report/tabulate
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.