Help using Base SAS procedures

Need help with array lookups

Reply
New Contributor
Posts: 4

Need help with array lookups

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 NameIndustry Code
Electrical001
Plumbing002
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 NaIndustry NameIndustry COde
Steve's PlumbingPlumbingElectrical

HELP!

Thanks

Respected Advisor
Posts: 3,887

Re: Need help with array lookups

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;

Super User
Super User
Posts: 7,392

Re: Need help with array lookups

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;

Super User
Posts: 9,671

Re: Need help with array lookups

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

Ask a Question
Discussion stats
  • 3 replies
  • 203 views
  • 0 likes
  • 4 in conversation