topic Re: problem in deleting matching recoreds from two datasets in SAS Programming

problem in deleting matching recoreds from two datasets

don21 — Fri, 09 Mar 2018 16:04:15 GMT

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:

Data set B

want data set C

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

Re: problem in deleting matching recoreds from two datasets

RW9 — Fri, 09 Mar 2018 16:08:33 GMT

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.

Re: problem in deleting matching recoreds from two datasets

don21 — Fri, 09 Mar 2018 17:03:58 GMT

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

Re: problem in deleting matching recoreds from two datasets

Bhushan — Fri, 09 Mar 2018 17:49:27 GMT

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;

Re: problem in deleting matching recoreds from two datasets

SuryaKiran — Fri, 09 Mar 2018 18:26:22 GMT

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;

Re: problem in deleting matching recoreds from two datasets

ballardw — Fri, 09 Mar 2018 19:44:18 GMT

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

Re: problem in deleting matching recoreds from two datasets

RW9 — Mon, 12 Mar 2018 09:21:41 GMT

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-data-AKA-generate/ta-p/258712

Re: problem in deleting matching recoreds from two datasets

MG18 — Mon, 12 Mar 2018 09:39:12 GMT

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

Re: problem in deleting matching recoreds from two datasets

don21 — Mon, 19 Mar 2018 13:25:43 GMT

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!