topic Re: retaining values in a %do loop in SAS Programming

retaining values in a %do loop

deleted_user — Tue, 19 Jan 2010 18:29:18 GMT

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

Re: retaining values in a %do loop

Flip — Tue, 19 Jan 2010 18:40:50 GMT

How about
if substr(company_identifer , 1, 6) in ( &id ) then output;

just create your list for ID quoted with a comma delimiter.

Re: retaining values in a %do loop

Flip — Tue, 19 Jan 2010 18:59:05 GMT

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;

Re: retaining values in a %do loop

deleted_user — Tue, 19 Jan 2010 19:33:52 GMT

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...

Re: retaining values in a %do loop

sbb — Tue, 19 Jan 2010 20:20:25 GMT

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.

Re: retaining values in a %do loop

Cynthia_sas — Tue, 19 Jan 2010 20:54:37 GMT

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

Re: retaining values in a %do loop

DanielSantos — Wed, 20 Jan 2010 08:53:07 GMT

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

Re: retaining values in a %do loop

deleted_user — Wed, 20 Jan 2010 15:35:36 GMT

Hi Cynthia,

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

Many thanks

Re: retaining values in a %do loop

deleted_user — Wed, 20 Jan 2010 15:37:15 GMT

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.