topic Re: Proc SQL Unique Counts and Categories in SAS Programming

Proc SQL Unique Counts and Categories

hwangnyc — Tue, 15 Nov 2016 15:59:14 GMT

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!

Re: Proc SQL Unique Counts and Categories

PeterClemmensen — Tue, 15 Nov 2016 16:02:33 GMT

Use PROC FREQ? 🙂

Re: Proc SQL Unique Counts and Categories

PeterClemmensen — Tue, 15 Nov 2016 16:25:31 GMT

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

Re: Proc SQL Unique Counts and Categories

Oligolas — Tue, 15 Nov 2016 16:30:50 GMT

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

Re: Proc SQL Unique Counts and Categories

hwangnyc — Tue, 15 Nov 2016 17:24:41 GMT

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.

Re: Proc SQL Unique Counts and Categories

Ksharp — Wed, 16 Nov 2016 05:07:20 GMT

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

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