Help using Base SAS procedures

Macro to delete observation in a dataset from another dataset

Reply
New Contributor
Posts: 3

Macro to delete observation in a dataset from another dataset

Hi,

I am trying to delete observation from one dataset based on the observations from another

Ex :

Dataset1
ObsVaraible1
1IC006218  Y134
2IC006219  Y158
3IC006216  Y1340001

Dataset2
ObsVaraible2
1Y134
2Y134001

Here based on dataset2 observations ( in varaiable2), it must delete the entries in dataset 1. so ideally observations 1 & 3  must be deleted in dataset1.

I believe this code should be looped and macro needs to be created for this. Appreciate your help.

Any other suggestions are also welcomed.

Data dataset3;

  Set dataset1;

IF INDEXW(varaible1,"Y134") >0 THEN delete;

Run;

Thanks,

Hari Prasad

Regular Contributor
Posts: 191

Re: Macro to delete observation in a dataset from another dataset

Posted in reply to HariPrasad

Quick fix using sql:

Deleting rows from dataset:

proc sql;

delete from dataset1 where variable2 in (select distinct variable2 from dataset2);

quit;

If you want to create a new dataset:

proc sql;

create table dataset3 as

   select * from dataset1 where variable2 not in (select distinct variable2 from dataset2);

quit;

   

//Fredrik

Contributor
Posts: 70

Re: Macro to delete observation in a dataset from another dataset

Posted in reply to HariPrasad

try below code...this may help...im not executed ..

data test1;

input variable1 $20.;

cards;

IC006218  Y134

IC006219  Y158

IC006216  Y1340001

;

run;

proc sql;

select count(variable1) into :count separated by ',' from test1;

select variable1 into :var1 - :var&count. from test1;

quit;

%put &count;

data test2;

input variable2 $20.;

cards;

Y134

Y1340001

;

run;

proc sql noprint;

select count(distinct(variable2)) into :count1 separated by ',' from test2;

select variable2 into :var_del1 - :var_del&count1.  from test2;

quit;

%put &count1;

%put &var_del1 &var_del2;

%let x= 1;

%put &&var_del&x;

%macro want;

Data test3;

  Set test1;

%do i = 1 %to  &count.;

   %do j = 1 %to &count1.;

IF INDEXW(variable1,"&&var_del&j..") gt 0 THEN delete;

    %end;

%end;

Run;

%mend;

%want;

Super Contributor
Posts: 276

Re: Macro to delete observation in a dataset from another dataset

Posted in reply to HariPrasad

Hello,

If i understand you requirement correctly,here is the solution which is specific to your requirement .You can do manipulations to bellow code according to your requirement .

data test1;

input variable2 $20.;

cards;

Y134

Y1340001

;

run;

proc sql;

select variable2 into :list separated by '","' from test1;

quit;

data test2;

input variable1 $20.;

cards;

IC006218  Y134

IC006219  Y158

IC006216  Y1340001

;

run;

Data T3;

set test2;

if scan(variable1,2) in ("&LIST") then delete;

run;

Best Regards.

Sanjeev.K

Contributor
Posts: 70

Re: Macro to delete observation in a dataset from another dataset

Posted in reply to kuridisanjeev

If we does not know the position, we can not able to use scan function i guess..

Ask a Question
Discussion stats
  • 4 replies
  • 934 views
  • 0 likes
  • 4 in conversation