topic Re: Filter Data and delete duplicates in SAS Studio

Filter Data and delete duplicates

jozuleta — Sun, 07 Oct 2018 21:45:20 GMT

Hi all,

I created a table and filtered out the rows which have blanks in columns gvkey , ibitic , isin. Now I want to delete all the duplicate rows which have identical data for column gvkey. In other words... I would like to keep just the rows with unique values for gvkey

My code until now:

PROC SQL;
 CREATE TABLE WORK.COMP_DataUS AS
 SELECT gvkey , ibtic , isin , sedol
 FROM COMP.SECURITY;
RUN;
QUIT;
data WORK.COMP_DataUS;
 set COMP_DataUS;
 where not missing(gvkey) AND(ibtic) AND(isin);
run; /* Output: 24,936 rows */

this is how my dataset looks like:

Basically I would like to have a table without the the blue columns (but for the whole table and not just for these two examples).

Thanks in advance for the support.

Best regards

Jorge

Re: Filter Data and delete duplicates

PaigeMiller — Sun, 07 Oct 2018 22:09:29 GMT

UNTESTED CODE

Assumes the data set COMP_DATAUS is sorted by GVKEY

proc freq data=comp_dataus;
    table gvkey/noprint out=_a_;
run;

data want;
    merge comp_dataus _a_;
    by gvkey;
    if count>1 then delete;
run;

Re: Filter Data and delete duplicates

singhsahab — Mon, 08 Oct 2018 17:26:45 GMT

I'm hoping this will work for you !!

proc sort data=COMP_DataUS nodup out=want;
by gvkey;
where gvkey is not missing;
run;

Re: Filter Data and delete duplicates

jozuleta — Mon, 08 Oct 2018 23:11:21 GMT

a good other way! Thanks, too!