Hi, I'm trying to convert proc sort nodupkey to proc sql with a counter of distinct rows defined by 2 variables. Dataset: Have key mode time x y z 1 int 1 a b c 1 pho 2 d a b 2 int 1 c v a 3 pho 1 a b c 3 pho 2 s d e Want key mode time x y z 1 int 1 a b c 2 int 1 c v a 3 pho 1 a b c For proc sort, I first sorted the dataset by key, mode and time proc sort data = have; by key mode time; run; Then to remove duplicates, I would like to take the earliest time, which has already been taken care of by the previous proc sort: proc sort data = have out = want nodupkey; by key mode; run; I need to convert this procedure to proc sql with a counter that counts the distinct combo of key and mode and produce the same output This is what I have, but is doesn't generate same obs number as proc sort: proc sql;
CREATE TABLE want AS
SELECT *, COUNT(DISTINCT(key||mode)) AS counter FROM want GROUP BY key, mode;
quit; The following will produce the same number of obs as proc sort, but it will only let me keep key and mode: proc sql;
create table want as
select key, mode, count(distinct key||mode) as counter from (select distinct * from have) group by key,mode; quit;
Grateful for any help!
... View more
