topic Proc sql to count distinct distinct subject or distinct keys in SAS Programming

Proc sql to count distinct distinct subject or distinct keys

Kc2 — Thu, 14 Mar 2019 13:18:58 GMT

data have;

input ID1 $ ID2 $;

cards;

A O

B A

C D D C

E P

F . G F H B I K I L I M ;

run;

I my dataset I have 9 distinct ID1 and and 5 distinct ID2. I want to calculate the total number of distinct id in column ID1 or ID2 . The count should be 14 .

I am wondering if this can be done in proc sql instead of proc freq? If this can be done in proc sql what would the code be?

thanks

Re: Proc sql to count distinct distinct subject or distinct keys

gamotte — Thu, 14 Mar 2019 13:32:27 GMT

Hello,

Something like this ?

proc sql;
SELECT sum(count(DISTINCT ID1), count(DISTINCT ID2)) 
FROM have;
quit;

Re: Proc sql to count distinct distinct subject or distinct keys

Kc2 — Thu, 14 Mar 2019 13:46:19 GMT

No this is not correct because ID2 has some ID1 which are in the column ID1. I only want the total of IDs which are unique to ID1 ad ID2, which would be (A,B,C,D,E,F,G,J ,I ,K,L,M,P,O= count=14).

The sum will sum the unique ID1 and Unique ID2

Re: Proc sql to count distinct distinct subject or distinct keys

gamotte — Thu, 14 Mar 2019 13:51:28 GMT

OK, the data step you gave in your question has some formatting problems so your

goal was unclear.

Try :

proc sql;
SELECT count(DISTINCT ID)
FROM (
    SELECT ID1 AS ID FROM have
	UNION
	SELECT ID2 AS ID FROM have
);
quit;

Re: Proc sql to count distinct distinct subject or distinct keys

andreas_lds — Thu, 14 Mar 2019 14:07:14 GMT

@Kc2 if the code suggested by @gamotte does not solve the problem, please re-post the data step, this time using the running-man-icon to preserve formatting.

Re: Proc sql to count distinct distinct subject or distinct keys

Kc2 — Thu, 14 Mar 2019 14:18:16 GMT

Thank you it worked