BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SherriF
Obsidian | Level 7

Hi,

 

I have two data sets. One containes a list of formal fortuen 500 companies' name. The other contains patent information for a lot of companies which are extracted online, so the format of the companies' name may differ from the first data set, even if they refer to the same company. 

 

What I wanted to do is to select out the patent information for the companies who are among the fortune 500 companies. So, I have to compare the compare the names between the two data sets. I used soundslike (=*) statemete, and treat each of the fortune 500 companies as a macro variable. As the follwoing code shows:

 

data _null_;
set company0;
suffix=put(_n_,5.);
call symput(cats('company',suffix), company);
run;

 

data selected_patent;
set patent0;
where organization_name =* "&company1.";
company=put("&company1.", 25.);
run;

 

&company1. stands for the one of the fortune companies, but I have 500 macro variables like this. 

 

one way I can think of is to run macro program, but I have to list 500 call statement for macro program!!!!!!

 

Is there any easy way to finish this task, instead of using calling macro program for 500 times?

 

Thanks,

Sherri

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20
Am I missing something, or can't this be solved in a simple SQL join?
Data never sleeps

View solution in original post

5 REPLIES 5
Reeza
Super User

I usually recommend the solution in this thread, primarily FriedEgg's. He also, suggests a open source tool called the Link King, if you can install software. That's usually not an option where I work.

 

https://communities.sas.com/t5/SAS-Procedures/Name-matching/td-p/82780

 

Otherwise, you might want to consider a data step solution if that's an option. You can load the names into a temporary and loop through them, there should be code that I wrote for that solution somewhere on here. 

 

Calling a macro 500 times is easy if you use call execute, but not efficient. 

LinusH
Tourmaline | Level 20
Am I missing something, or can't this be solved in a simple SQL join?
Data never sleeps
SherriF
Obsidian | Level 7
No, you didn't miss anything. You are right! I lost myself!!

Thank you!
Sherri
Reeza
Super User

Yes, it's a straightforward SQL, but the next step is typically how do I do this 'fuzzy' comparison with different options which is what FriedEgg's solution covers, using SOUNDEX, COMPGED and other comparison operators.

SherriF
Obsidian | Level 7
Good resource! Thank you so much! Learned new stuff!!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 5 replies
  • 962 views
  • 1 like
  • 3 in conversation