DATA Step, Macro, Functions and more

How do I delete observations with IF/THEN during Merge

Reply
Occasional Contributor
Posts: 15

How do I delete observations with IF/THEN during Merge

[ Edited ]

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

Super User
Posts: 2,037

Re: How do I delete observations with IF/THEN during Merge

Posted in reply to awmeyertimmy

How about:

 

	if not(a and  b);
Occasional Contributor
Posts: 15

Re: How do I delete observations with IF/THEN during Merge

Posted in reply to novinosrin

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

Super User
Posts: 2,037

Re: How do I delete observations with IF/THEN during Merge

[ Edited ]
Posted in reply to awmeyertimmy

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

Super User
Posts: 13,868

Re: How do I delete observations with IF/THEN during Merge

Posted in reply to awmeyertimmy

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;
Super User
Posts: 8,213

Re: How do I delete observations with IF/THEN during Merge

Posted in reply to awmeyertimmy

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

 

Occasional Contributor
Posts: 15

Re: How do I delete observations with IF/THEN during Merge

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

 

Super User
Posts: 10,519

Re: How do I delete observations with IF/THEN during Merge

Posted in reply to awmeyertimmy

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 30

Re: How do I delete observations with IF/THEN during Merge

Posted in reply to awmeyertimmy

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.

 

Occasional Contributor
Posts: 15

Re: How do I delete observations with IF/THEN during Merge

Like this?

 

DATA Junk.Internet3;
		Merge Junk.Internet2 (in=a)
				Junk.Inactivemktr (in=b);
				By CompanyCode;
		if b then delete;
RUN;
Super User
Posts: 8,213

Re: How do I delete observations with IF/THEN during Merge

Posted in reply to awmeyertimmy

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

 

Ask a Question
Discussion stats
  • 10 replies
  • 228 views
  • 0 likes
  • 6 in conversation