topic Re: Match and remove rows in New SAS User

Match and remove rows

NewUsrStat — Fri, 12 Aug 2022 08:58:30 GMT

Hi guys, is there a way to remove rows in a file that are not present in a second file by matching the column ID?

Thank you in advance

For example suppose to have:

data set1

ID AGE Role

001 35-50 A

002 55-60 B

003 35-50 C

004 35-50 D

005 35-50 E

and data set2

ID Hours Vacancies

002 900 21

003 456 54

005 45 0

I would like the data set1 to become:

ID AGE Role

002 55-60 B

003 35-50 C

005 35-50 E

Re: Match and remove rows

PeterClemmensen — Fri, 12 Aug 2022 08:52:30 GMT

Yes there is. Please show us some data. Makes it much easier to provide a usable code answer.

Re: Match and remove rows

Kurt_Bremser — Fri, 12 Aug 2022 09:14:03 GMT

Use a hash:

data want;
set ds1;
if _n_ = 1
then do;
  declare hash ds2 (dataset:"ds2");
  ds2.definekey("id");
  ds2.definedone();
end;
if ds2.check() = 0; /* CHECK method returns zero if successful */
run;

Untested; for tested code, provide example data in data steps with datalines.

Re: Match and remove rows

PeterClemmensen — Fri, 12 Aug 2022 10:27:32 GMT

Here is two different approaches with the same result

/* Example Data */
data set1;
input ID $ AGE $ Role $;
datalines;
001 35-50 A 
002 55-60 B 
003 35-50 C 
004 35-50 D 
005 35-50 E 
;

data set2;
input ID $ Hours Vacancies;
datalines;
002 900 21 
003 456 54 
005 45  0  
;

/* Proc SQL Approach */
proc sql;
   create table want as
   select set1.*
   from set1, set2
   where set1.ID = set2.ID
   ;
quit;

/* Data Step Hash Approach */
data want;
   if _N_ = 1 then do;
      dcl hash h(dataset : 'set2');
      h.definekey('ID');
      h.definedone();
   end;

   set set1;

   if h.check() = 0;
run;

Re: Match and remove rows

Ksharp — Fri, 12 Aug 2022 12:06:47 GMT

/* Example Data */
data set1;
input ID $ AGE $ Role $;
datalines;
001 35-50 A 
002 55-60 B 
003 35-50 C 
004 35-50 D 
005 35-50 E 
;

data set2;
input ID $ Hours Vacancies;
datalines;
002 900 21 
003 456 54 
005 45  0  
;

/* Proc SQL Approach */
proc sql;
   create table want as
   select set1.*
   from set1
   where ID in (select ID from set2)
   ;
quit;

Re: Match and remove rows

mkeintz — Fri, 12 Aug 2022 12:23:02 GMT

Are the data sorted? If so, and if data set 2 never has more than one observation per id, then:

data want;
  merge ds1 (in=in1)    ds2 (keep=id  in=in2);
  by id;
  if in1=in2;
run;