/* Sample program to convert a request file containing mnemonics
as the security identifier to the Datastream 6-character code
required by EventStream 2002. 

The output of this program consists of:
- SAS data set request_dscd at the end of step 2 contains the 
input request file plus DSCDs. It can be printed, used as
input to EventStream's dsmacromaker statement, or exported to
a different type of file.
- If any of the original request file rows have been matched to 
two or more DSCDs, step 3 prints the rows in the SAS Output
window. The user should decide which of the repeat lines to 
remove from the request_dscd data set by comparing the firm
names to the original sample.
- If any of the original request file rows could not be matched
to DSCDs, step 4 prints the rows in the SAS Output window. The user
may be able to manually look up DSCDs in Datastream.

This program can be downloaded from 
the Examples area of www.eventstudy.com. */

* Step 1 -- Read in your existing mnemonic-based request file.
The mnem variable must be a character variable of length  12.
If you already have a unique ID variable (numeric or character)
on each row, you can use it instead of sample_id. In that case, 
change sample_id to your variable name throughout the code.

Additonal variables can be included in the request_mnem data set
if desired.;

data request_mnem(index=(mnem sample_id));
 length mnem $ 12;
 infile datalines; /* can point to a separate file instead if desired */
 input mnem:$6. eventdat:yymmdd8.;
 /* can replace infile and input by set if already have a SAS data set */
 sample_id+1;
 format eventdat yymmddd10.;
 datalines; /* remove line if not listing sample here */
J:JK@N   19930607
J:POOK   19930907
J:NODO   19931110
KO:SNM   19950516
J:NJ@N   19960328
T:KEPP   19960411
T:OTAL   19960517
T:NATE   19990623
J:RAMO   19990831
J:EB@N   19990901
J:ACHM   19990908
J:KUSS   19990916
J:UTOS   19991012
;
run;

* Step 2 -- Automatically match as many mnemonics to dscd codes
as possible. The SAS library name ds must point to the folder
containing EventStream data sets. There should be a libname 
statement at the beginning of 
\Program Files\EventStream\testeventstream.dsmacro.sas
that can be copied to here if needed.;

proc sql;
 create table request_dscd as select * from 
  request_mnem,ds.eq_list(keep=bdate mnem dscd name)
 where request_mnem.mnem=eq_list.mnem
 and eventdat>=bdate;
 create index sample_id on request_dscd;
quit;

data request_unmatched;
 merge request_mnem request_dscd(in=matched);
 by sample_id;
 if not matched;
run;

proc sql;
 create table request_morematches as select * from
  request_unmatched,ds.eq_list(keep=bdate mnem2 dscd name2 rename=(mnem2=mnem name2=name))
  where eq_list.mnem ne ' ' and request_unmatched.mnem=eq_list.mnem
  and eventdat>=eq_list.bdate;
 create index sample_id on request_morematches;
quit;

data request_dscd;
 set request_dscd request_morematches;
 by sample_id;
run;

* Step 3 -- Check for duplicates;

proc summary data=request_dscd nway;
 class sample_id;
 var bdate;
 output out=request_duplicates(keep=sample_id n_peroriginal) n=n_peroriginal;
run;

data request_duplicates;
 merge request_dscd request_duplicates;
 by sample_id;
 if n_peroriginal>1;
 drop n_peroriginal;
run;

proc print data=request_duplicates;
 id sample_id;
 title 'Duplicates If Any';
run;

* Step 4 -- See if any are still not matched at all. Manual lookup in Datastream
may produce DSCDs for these.;

data request_unmatched;
 merge request_mnem request_dscd(in=matched);
 by sample_id;
 if not matched;
run;

proc print data=request_unmatched;
 id sample_id mnem;
 title 'Rows from Original Request File for which No DSCD Found';
run;