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..
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.