BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
don21
Quartz | Level 8

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..

1 ACCEPTED SOLUTION

Accepted Solutions
don21
Quartz | Level 8

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

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

don21
Quartz | Level 8

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..?

Bhushan
Calcite | Level 5

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;

ballardw
Super User

@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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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...

SuryaKiran
Meteorite | Level 14

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
don21
Quartz | Level 8

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!

MG18
Lapis Lazuli | Level 10

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

 

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 962 views
  • 1 like
  • 6 in conversation