Proc SQL Unique Counts and Categories

[hwangnyc]

Hi everyone,

I'd like to get a count of all the unique cases I have in a dataset. The code I am using is only giving me the counts but not the categories associated with it. Here is my code:

proc sql;
 select count(unique(ID)) as u_ID 'Number of Unique Ids'
 from MyData
 group by Age
;quit;

Here are my results:

Number of Number of Unique Ids
19
99
58
110
454
18
52
2
74
22
4
8
9
12
88
4
9
12

This is what I would like:

Age	Number of Number of Unique Ids
0	19
1	99
2	58
3	110
4	454
5	18
6	52
7	2
8	74
9	22
10	4
11	8
12	9
13	12
14	88
15	4
16	9
17	12

Any advice would be greatly appreciated! Thanks! 

[PeterClemmensen]

Use PROC FREQ? 🙂

[PeterClemmensen · Accepted solution]

Or

proc sql;
 select Age, count(unique(ID)) as u_ID 'Number of Unique Ids'
 from MyData
 group by Age
;quit;

[Oligolas]

Hi,

as #draycut proposed you should consider using proc freq.

ods select none;
ods output oneWayFreqs=want(keep=age frequency);
proc freq data=sashelp.class;
table age;
;
run;
ods select all;

Cheers

[hwangnyc]

Thanks for the response, proc freq would not work because I wanted the unique cases. Proc freq could give me the frequency for all cases.

[Ksharp]

Actually proc freq can do that. you need NLEVELS option;

proc freq ......    nlevels ;
........