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.
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
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;
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;
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.