Proc sql to count distinct distinct subject or distinct keys

[Kc2]

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

KC

[gamotte]

Hello,

Something like this ?

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

[Kc2]

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

[gamotte · Accepted solution]

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;

[Kc2]

Thank you it worked

[andreas_lds]

@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.