Need help with array lookups

[steveg1]

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.

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

[Patrick]

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;

[RW9]

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;

[Ksharp]

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