topic Re: How do I delete observations with IF/THEN during Merge in SAS Programming

How do I delete observations with IF/THEN during Merge

awmeyertimmy — Fri, 08 Dec 2017 23:28:45 GMT

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!

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

novinosrin — Fri, 08 Dec 2017 23:30:18 GMT

How about:

	if not(a and  b);

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

awmeyertimmy — Fri, 08 Dec 2017 23:34:48 GMT

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

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

novinosrin — Fri, 08 Dec 2017 23:38:00 GMT

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

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

ballardw — Fri, 08 Dec 2017 23:38:07 GMT

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;

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

art297 — Fri, 08 Dec 2017 23:41:12 GMT

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

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

bstarr — Fri, 08 Dec 2017 23:42:46 GMT

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.

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

awmeyertimmy — Fri, 08 Dec 2017 23:50:27 GMT

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,

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

awmeyertimmy — Fri, 08 Dec 2017 23:53:32 GMT

Like this?

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

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

art297 — Sat, 09 Dec 2017 00:06:48 GMT

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

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

Kurt_Bremser — Sat, 09 Dec 2017 09:10:05 GMT

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.