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
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
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;
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
If we does not know the position, we can not able to use scan function i guess..
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.