DATA Step, Macro, Functions and more

problem in deleting matching recoreds from two datasets

Accepted Solution Solved
Reply
Contributor
Posts: 72
Accepted Solution

problem in deleting matching recoreds from two datasets

Hi,

 

I am facing a problem in deleting matching observations in a dataset that are in match with another. I have used the below but they did not work:

 

Data set A:

ID

1

2

3

Data set B

2

3

 

want data set C

ID

1

 

I used:

/*************************************************/

proc sql;
create table tableC as select a.* from
tableA as a, tableB as b where a.id^=b.id;
quit;

/**************did not work***********************************/

and

/*************************************************/

proc sql;
delete from work.tableA
where ID IN (select ID from work.tableB);
quit;

/***************did not work**********************************/

and I also tried deleting them directly

/*************************************************/

proc sql;

delete from tableA where ID in ("2","3");

quit;

/*********************did not work****************************/

 

I also tried atleast selecting the common Ids

proc sql;

select * from tableA where ID in ("2","3");

quit;

this also did not work (but there are common IDs, I am sure. I dont understand the issue here. Please help. There are no errors in the log, the queries are just not deleting the common IDs.

 

Regards..


Accepted Solutions
Solution
‎03-19-2018 09:26 AM
Contributor
Posts: 72

Re: problem in deleting matching recoreds from two datasets

Posted in reply to SuryaKiran

Hi I have found teh root cause for my issue, it is nothing but both the ID columns which are currently in Char have been changed to Num and the code worked like charm.. I had the logics but was struggling due to this simple issue.

 

Thank you all for the support!

View solution in original post


All Replies
Super User
Super User
Posts: 9,227

Re: problem in deleting matching recoreds from two datasets

Unfortunately "did not work" does not tell us anything.  If you data looks like that then:

proc sql;
  create table want as 
  select * 
  from   a
  except 
  select * 
  from   b;
quit;

There are numerous other ways, merge by id if a and not b or b and not a for example in a datastep.

Contributor
Posts: 72

Re: problem in deleting matching recoreds from two datasets

Hi RW9,

 

by "did not work" I mean, the queries that I mentioned above did not give the desired result.

Those logics are not deleting the matching Ids the query runs and teh output dataset still contain sthe matching observations which should not be there. I suspect there is an internal format issue although the ID is in "char" in both the data sets.

 

Please advise, I cannot explain more because the query blindly does not work even though the logic is perfect. can this be some kind of technical issue?? if so, how can I resolve this..???

 

I mean a simple select * from tableA where ID in ("2","3") gives no result even though teh table has those Ids. What could be the reason for ths..?

Occasional Contributor
Posts: 9

Re: problem in deleting matching recoreds from two datasets

you are almost there , you have to be very careful while writing any codes

for small doubts you  should do trial and error until you get final Output but nevertheless 

 

here you go 


Data A;
input ID;
datalines;
1
2
3
;

Data B;
input ID;
datalines;
2
3
;
proc sql;
create table c as
select * From A
where id not in (select * from B);
quit;

Super User
Posts: 13,084

Re: problem in deleting matching recoreds from two datasets


@don21 wrote:

Hi RW9,

Please advise, I cannot explain more because the query blindly does not work even though the logic is perfect


I would suggest you consider restating "logic is perfect" as it that were the case the code would produce the desired results.

 

Often "good code with wrong results" comes from an actual misunderstanding of the data contained such as assuming values are "identical" when they aren't. Character values are notorious for containing blanks or other not printable characters, tab or null for example, that you don't quite see in a casual look at data. Or you expect a word to be in consistent case such as "Male" but the data actually contains "male", "MALE" and "mALE" which would not match "Male. In SAS another issue can come from comparing numeric that use the same display format such a F5.2 and values appear to be the same when viewed with such but the internal values differ and hence are not equal.

Super User
Super User
Posts: 9,227

Re: problem in deleting matching recoreds from two datasets

Then your data doesn't look like what you have posted.  This could be for many reasons, could it be formatted to look like numbers?  Could it be numbers?  Could it have spaces or other special characters.  I can't tell you as I can't see your data.  Perhaps try following this and seeing what the data really look like:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

Super Contributor
Posts: 478

Re: problem in deleting matching recoreds from two datasets

[ Edited ]

This maybe because of leading or trailing blanks. 

" 1" is not equal to "1" 

check this query:

Data TableA;
id=" 1";
run;
Data TableB;
id="1";
run;
proc sql;
create table tableC as 
select a.* from 
tableA as a, tableB as b where a.id^=b.id;
quit;

Check with adding STRIP() Function

 

proc sql;
create table tableC as 
select a.* from 
tableA as a, tableB as b where STRIP(a.id)^=STRIP(b.id);
quit;

proc sql;
delete from work.tableA
where STRIP(ID) IN (select STRIP(ID) from work.tableB);
quit;

proc sql;
delete from tableA where STRIP(ID) in ("2","3");
quit;

proc sql;
select * from tableA where STRIP(ID) in ("2","3");
quit;

 

Thanks,
Suryakiran
Solution
‎03-19-2018 09:26 AM
Contributor
Posts: 72

Re: problem in deleting matching recoreds from two datasets

Posted in reply to SuryaKiran

Hi I have found teh root cause for my issue, it is nothing but both the ID columns which are currently in Char have been changed to Num and the code worked like charm.. I had the logics but was struggling due to this simple issue.

 

Thank you all for the support!

Contributor
Posts: 69

Re: problem in deleting matching recoreds from two datasets

The below code merge will work for ur problem :-

data a;
input num 3.;
datalines;
1
2
3
;
run;

data b;
input num 3.;
datalines;
2
3
;
run;

data c ;
merge a b ;
by num;
run;

proc print data=c;
run;

 

output :-

Obs num

1 1

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 121 views
  • 1 like
  • 6 in conversation