BookmarkSubscribeRSS Feed
awmeyertimmy
Fluorite | Level 6

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

10 REPLIES 10
novinosrin
Tourmaline | Level 20

How about:

 

	if not(a and  b);
awmeyertimmy
Fluorite | Level 6

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

novinosrin
Tourmaline | Level 20

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
Super User

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
Opal | Level 21

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
Fluorite | Level 6

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
Super User

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

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
Fluorite | Level 6

Like this?

 

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

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

 

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
  • 10 replies
  • 4238 views
  • 0 likes
  • 6 in conversation