topic Need help with array lookups in SAS Procedures

Need help with array lookups

steveg1 — Mon, 09 Jun 2014 07:04:54 GMT

Hi:

I need some guidance on a SAS program I am trying to code.

I have a table of 18,000
industry names and codes. I want to load the data into a 2 dimensional
array of 2 columns X 18000 rows. The first column of the array will be
industry name and the second column of the array will be industry code.

Then I want to sequentially read a file of company names, and check to see if any word in the company name
matches an industry name. If it does I want to append the industry name
and number to the company name.

For example: Industry Name Table (18,000 rows)

Industry Name	Industry Code
Electrical	001
Plumbing	002
Masonry	003

Company name Input Table (1 field called company name):

Company Name
Steve's Plumbing

Desired Output (3 fields: company name, industry name, industry code)

Company Na	Industry Name	Industry COde
Steve's Plumbing	Plumbing	Electrical

HELP!

Thanks

Re: Need help with array lookups

Patrick — Mon, 09 Jun 2014 07:56:59 GMT

Some code like below could eventually do the job. The second case I've added to the data might also demonstrate some of the issues you will have to solve when dealing with real data.

data Industry;

infile datalines truncover dsd;

input Industry_Nm:$40. Industry_Cd:$6.;

Industry_Nm=propcase(Industry_Nm);

datalines;

Electrical,001

Plumbing,002

Masonry,003

;

run;

data Company_Name;

infile datalines truncover dsd;

input Company_Nm:$80.;

datalines;

Steve's Plumbing

The ELECTRICAL&PLUMBING Company

;

run;

data want;

if _n_=1 then

do;

if 0 then set Industry;

dcl hash h(dataset:'Industry');

_rc=h.defineKey('Industry_Nm');

_rc=h.defineData(all:'yes');

_rc=h.defineDone();

end;

call missing(Industry_Nm, Industry_Cd);

set Company_Name;

length _word $80;

do _i=1 by 1;

_word=scan(Company_Nm,_i);

if missing(_word) then leave;

_word=propcase(_word);

put _word;

if h.find(key:_word)=0 then leave;

end;

run;

Re: Need help with array lookups

RW9 — Mon, 09 Jun 2014 09:52:14 GMT

Hi,

Sorry, you don't mention what your Industry data looks like, or what the file is you are reading in. Therefore I will assume the data is as it looks above, and the file is Excel (note I have not tested this):

libname myexcel excel "an excel file.xls";

proc sql;

create table WANT as

select COMPANY.NAME,

INDUSTRY.NAME,

INDUSTRY.CODE

from MYEXCEL.'sheet1'n COMPANY

left join INDUSTRY_NAME_TABLE INDUSTRY

on index(COMPANY.NAME,INDUSTRY.NAME)>0; /* Note you may want to upcase both, or you may not */

quit;

Re: Need help with array lookups

Ksharp — Mon, 09 Jun 2014 13:26:52 GMT

I think Patrick's code is very good. and if your table is not large , you also can get it by SQL.

data Industry;
  infile datalines truncover dsd;
  input Industry_Nm:$40. Industry_Cd:$6.;
  Industry_Nm=propcase(Industry_Nm);
  datalines;
Electrical,001
Plumbing,002
Masonry,003
;
run;
 
 
data Company_Name;
  infile datalines truncover dsd;
  input Company_Nm:$80.;
  datalines;
Steve's Plumbing
;
run;

proc sql;
create table want as
 select * from Industry,Company_Name
  where Company_Nm contains strip(Industry_Nm);
  quit;

Xia Keshan