Accepted Solutions
Hi @Agent1592
In cases like this, I would use another approach, the use of a lookup list instead of using algoritms to identify CEO's and Chairmen. It is a little more complicated and maybe overkill in your case, but I normally work with production code, where data quality is a major concern. The reason is that
- even with 100 records as in your exampe, it is difficult to determine if the algoritm covers all occurences, and with thousends of records it becomes impossible.
- if input changes, and other title variants occurs, I would like some sort of notification so the code could be changed accordingly.
So here we go. Input is copied from your post and not repeated here.
First part is to create a distinct list of titles. A data step is used to split the title string in individual titles with one opuput record per title.:
data test2 (drop=i); set test;
length w $250 title $100;
w = tranwrd(upcase(tranwrd(titleann,' and ',',')),'&',',');;
do i = 1 to count(w,',')+1;
title = left(upcase(scan(w,i,',')));
output;
end;
run;
proc sql;
create table titles as
select distinct title
from test2;
quit;
data _null_; set titles;
put '0 ' title;
run;
the result is a list written to the log, 5 out of 19 lines shown:
0 ACTING CEO
0 BENEFITS COMMITTEE
0 CEO
0 CHAIRMAN
0 CHAIRMAN OF EXECUTIVE COMMITTEE
Then I copy the list from the LOG window to data step code in the Program Editor and change code values from "0" to "1" (CEO) or "2" (Chairman) where appropriate. The result is this code, which should wtite to a permanent data set in production code.
data titlevalue;
infile cards truncover;
input value title $100.;
cards;
0
0 ACTING CEO
0 BENEFITS COMMITTEE
1 CEO
2 CHAIRMAN
2 CHAIRMAN OF EXECUTIVE COMMITTEE
2 CHAIRMAN OF THE BOARD
1 CHIEF EXECUTIVE OFFICER
2 CHMN
2 CHMN.
0 CONSULTANT
0 DIRECTOR
2 EXECUTIVE CHAIRMAN
0 MEMBER OF CORPORATE COMPENSATION
0 MEMBER OF EXECUTIVE COMMITTEE
0 PRES.
0 PRESIDENT
0 V-P
0 VICE CHAIRMAN
;
run;
Now we are ready to use the list as a lookup table. The following code reads the original input table and is a veriant of the code used above to split the titles, but here it doesn't split observations, byt identifies CEO and chairmen AND reports titles not accounted for. Note that I added an extra line to the input before running to show the handling of unknown titles:
data ceochair (keep=id year titleann ceo_chair);
length w $250 title $100 value 8;
if _N_ = 1 then do;
declare hash h(dataset:'work.titlevalue');
h.defineKey('title');
h.defineData('value');
h.defineDone();
call missing(value);
end;
set test;
w = tranwrd(upcase(tranwrd(titleann,' and ',',')),'&',',');;
do i = 1 to count(w,',')+1;
title = left(upcase(scan(w,i,',')));
rc=h.find();
if value = 2 then ceo = 1;
if value = 1 then chair = 1;
if value = . then put 'INFO: Unknown title: ' id= year= title=;
end;
if ceo = 1 and chair = 1 then ceo_chair = 1;
run;
The log shows the result:
Part of the result data set - actually the coding might be inaccurate, because I made errors in the the lookup table, but the point is that it follows the rules set in the table, not in an error-prone algoritm:
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
-
1423 views
-
2 likes
-
-
