BookmarkSubscribeRSS Feed
HariPrasad
Calcite | Level 5

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

4 REPLIES 4
FredrikE
Rhodochrosite | Level 12

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

yaswanthj
Calcite | Level 5

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;

kuridisanjeev
Quartz | Level 8

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

yaswanthj
Calcite | Level 5

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

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2290 views
  • 0 likes
  • 4 in conversation