topic Macro to delete observation in a dataset from another dataset in SAS Procedures

Macro to delete observation in a dataset from another dataset

HariPrasad — Tue, 10 Sep 2013 09:09:44 GMT

Hi,

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

Ex :

Dataset1
Obs	Varaible1
1	IC006218 Y134
2	IC006219 Y158
3	IC006216 Y1340001

Dataset2
Obs	Varaible2
1	Y134
2	Y134001

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

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

FredrikE — Tue, 10 Sep 2013 09:38:02 GMT

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

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

yaswanthj — Tue, 10 Sep 2013 12:45:47 GMT

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;

Run;

%mend;

%want;

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

kuridisanjeev — Tue, 10 Sep 2013 13:11:10 GMT

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

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

yaswanthj — Tue, 10 Sep 2013 13:22:16 GMT

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