BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi,

I have the following data:

sys_id company_name company_identifer state
1 ABC Co. 245679762 MI
2 XYZ Co. 876234T87 OH
3 WALDEN 88723O124 MA
4 GOOGLE 77823I457 CA
5 TALI 44321H887 AZ
.
.
.

I have about 10,000 or so obs similar to the ones above. My question is if I need to lookup a company based on the first 6 digits or chars of the company id since I'm not sure about the complete 9 digit, how would I go about implementing that. Also the company_id is passed dynamically rather than hard-coded.

So you have a flat file that has the following ids:

245679
876234
88723O
77823I
44321H

These ids are passed on to a macro var where the program then separates the list and takes one id at a time and do a comparison against the dataset. So if the first 6 digits or chars matches the ID then output the obs.

I know this can be done by implementing something like:

if company_identifier =: &6_digit_id then output;

I was only able to make this work for 1 id. However when I code in a do loop I only get the last ID (which is obvious) and I cant seem to find a way to retain the previous IDs that have been passed in. How do I modify the code so I can have it implemented for multiple ID's such that the incoming id value of 6 digits or chars is compared against the datasets 9 digit ID and if the first 6 matches then output those obs.

Something like:
%main macro;
....
...

data xyz;
%do i = 1 to %count(&id);
%let q = %scan(&id,i);
if company_identifier =: &id then output;
....
....
...
end;
run;

I'm not sure what I'm missing. Your help is much appreciated.

Regards,
Joe
8 REPLIES 8
Flip
Fluorite | Level 6
How about
if substr(company_identifer , 1, 6) in ( &id ) then output;

just create your list for ID quoted with a comma delimiter.
Flip
Fluorite | Level 6
Or just for fun.

data start;
input
sys_id company_name $ company_identifer $ state $;
cards;
1 ABC 245679762 MI
2 XYZ 876234T87 OH
3 WALDEN 88723O124 MA
4 GOOGLE 77823I457 CA
5 TALI 44321H887 AZ
run;


data look1 (index = (frst6));
input frst6 $;
cards;
245679
88723O
77823I
44321H
run;


data end;
set start;
drop frst6;
frst6 = substr(company_identifer, 1,6);
set look1 key = frst6/unique;
if (not _iorc_ = %sysrc(_sok)) then do;
delete;
_error_ = 0;
end;
run;
deleted_user
Not applicable
Hi,

Thank you so much.. the first solution with substr seems a better and a more simple approach..

I'm gonna try with the substr .. hopefully that should resolve it...
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
As compared to IF/THEN with the IN operator, you should also consider the performance benefit with using a PROC FORMAT and loading it with the CNTLIN= option, presuming you don't have any string-prefix duplicates. The DATA step code then would have a single line assignment statement with a PUT function reference. Fewer moving parts and no macro variable coding necessary.

Scott Barry
SBBWorks, Inc.
Cynthia_sas
SAS Super FREQ
And, in addition to all the other suggestions, if all you want is an output file of matches, then depending on the sizes involved, either a DATA Step MERGE or an SQL JOIN would also produce the desired output without macro code involvement.

I don't actually see much need for macro coding as you have described your data and end result.

cynthia
DanielSantos
Barite | Level 11
Yep, agree with Cynthia.
[pre]
proc sql noprint;
create table OUTDATA as
select a.* from DATA as a, LOOKUP as b
where b.COMPANY_IDENTIFIER like cats(a.COMPANY_IDENTIFIER,'%');
quit;
[/pre]
Or you could also use the substr function to do the match.
[pre]
where a.COMPANY_IDENTIFIER eq substr(b.COMPANY_IDENTIFIER,1,6);
[/pre]
Cheers from Portugal.

Daniel Santos @ www.cgd.pt
deleted_user
Not applicable
Hi Santos,

This is awesome. Thanks a million for your help. Implementing the proc sql step has proved to me much efficient for me.

Once again many many thanks to you and Cynthia, and all you guys.

You guys are absolutely fantastically, awesome.
deleted_user
Not applicable
Hi Cynthia,

Thanks a bunch for the input. This helped me a lot.

Many thanks

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
  • 8 replies
  • 840 views
  • 0 likes
  • 5 in conversation