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
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;
Hello,
Something like this ?
proc sql;
SELECT sum(count(DISTINCT ID1), count(DISTINCT ID2))
FROM have;
quit;
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
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;
Thank you it worked
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: