How do I delete observations with IF/THEN during Merge

[awmeyertimmy]

I have two data sets.  Data set A contains all of my data.  Data set B includes company codes that I want to delete from data set A.  What I want to do is, If company code exists in data set B then delete all observations in data set A that have the same company code.  Here is what I have so far, but I know my if statement within the merge is incorrect. I'm using SAS 9.4

Proc Sort Data=Junk.Internet2;
by CompanyCode MarketerCode;
run;
Proc Sort Data=Junk.Inactivemktr;
by CompanyCode MarketerCode;
run;
DATA Junk.Internet2;
		Merge Junk.Internet2 (in=a)
				Junk.Inactivemktr (in=b);
				By CompanyCode MarketerCode;
		if a and not b;
RUN;

Can anyone help?

Thanks!

a

[novinosrin]

How about:

	if not(a and  b);

[awmeyertimmy]

Unfortunately that did not work. I'm still getting observations in my data set that should be excluded (deleted) in the data step

[novinosrin]

can you give a sample of your input and wanted output data?

and i think your approach is right. I don't know why you are merging by two by variables when the check is company code only.  Obviously, i don;t know your daata, so it's difficult to tell but merging by just company code with if a and not b should work i think

[ballardw]

If Marketercode variable has no role in the deletion then do not include it in the BY statement. If it does then explicitly describe the role it has.

If you have actually run this code it is likely that you have to rebuild your junk.internet2 set from scratch as you have used it as both a source and result in the same data set.

If you have multiple values of company code in both Internet2 and Inactivemktr then a data step merge may not be tool you need as it can have problems with many-to-many matches. If the company code only appears once in the Inactivemktr then this might work (AFTER rebuilding Internet2.

DATA Junk.Internet3;
   Merge Junk.Internet2 (in=a)
   	 Junk.Inactivemktr (in=b);
   By CompanyCode ;
   if b then delete;
RUN;

[art297]

Your code looks correct if you are trying to delete marketers within companies. But that isn't what you said you wanted to do.

Do you want to delete all companies if one of its marketers is in the inactivemktr file?

Also, regardless of your response, your code currently wrecks the junk.internet2 file, thus if your code is wrong that file will be corrupted. I strongly suggest creating a new file, rather than replacing the existing file.

Art, CEO, AnalystFinder.com

[awmeyertimmy]

Thanks Art.  Yes, I incorrectly stated my intentions.  I want to delete marketers within companies. E.g. each company has multiple marketer codes.  Example:

Obs | Company  |  Marketer

1      |A               |  001

2      |A               |  002

3      |A               |  003

4      |A               |  004

Marketer codes 002 and 004 are inactive so I want to delete observations 2 and 4  when merging in that data step.

Make sense?

Thanks,

a

[Kurt_Bremser]

You need to supply example data for both datasets with observations where your code does not work as intended. Do so in data steps with datalines, so that it is easy for your possible helpers to recreate the datasets.

[bstarr]

You're merging by CompanyCode and MarketerCode. Therefore if you have CompanyCode "A" and MarketerCode "A" in dataset1 and CompanyCode "A" and MarketerCode "B" in dataset2, the resulting dataset will still have CompanyCode "A" and MarketerCode "A" since MarketerCode was not "B". Make sense? Can you merge by CompanyCode only? That's the easiest way to get it to work. You could probably do it in PROC SQL otherwise.

[awmeyertimmy]

Like this?

DATA Junk.Internet3;
		Merge Junk.Internet2 (in=a)
				Junk.Inactivemktr (in=b);
				By CompanyCode;
		if b then delete;
RUN;

[art297]

No! I think your original code would do what you're trying to do .. as long as both company and marketercode are in both files.

Art, CEO, AnalystFinder.com