You're not getting what I mean by lookup within a datastep Paige... data Lookup;
infile datalines dsd missover;
input county $ town $ school $ class $ start_date :ddmmyy10. end_date :ddmmyy10. Value_To_Return;
format start_date end_date date9.;
cards;
"Essex", "Braintree", "Tabor", "Blue", 12/10/2017, 31/07/2018, 100
"Essex", "Braintree", "Tabor", "Blue", 01/08/2018, 31/12/2018, 150
"Essex", "Braintree", "Tabor", "Blue", 01/01/2019, 15/04/2019, 125
"Essex", "Braintree", "Tabor", "Blue", 16/04/2019, 31/12/2099, 140
"Essex", "Braintree", "Tabor", "Red", 12/10/2017, 31/07/2018, 120
"Essex", "Braintree", "Tabor", "Red", 01/08/2018, 31/12/2018, 140
"Essex", "Braintree", "Tabor", "Red", 01/01/2019, 15/04/2019, 130
"Essex", "Braintree", "Tabor", "Red", 16/04/2019, 31/12/2099, 140
;
run;
Data Have;
infile datalines dsd missover;
input county $ town $ school $ class $ eventdate :ddmmyy10.;
format eventdate date9.;
cards;
"Essex", "Braintree", "Tabor", "Blue", 15/3/2019
"Essex", "Braintree", "Tabor", "Blue", 01/10/2018
"Essex", "Braintree", "Tabor", "Red", 12/01/2019
;
run; For this example, I would want to be working with the Have dataset and get the Value_To_Return where all 4 character variables match and also where eventdate was <= start_date and <= end_date. The data in these instances would return 125 on row 1, 150 on row 2 and 130 on row 3. I have written the Proc FCMP that looks up the character variables, but I dont know how to adapt it to include the date range lookup as well. proc fcmp outlib=work.functions.samples;;
function get_score(county $, town $, school $, class $);
declare hash Lkup(dataset: "work.Lookup");
rc = lkup.definedata("Value_To_Return");
rc=lkup.definekey("county", "town", "school", "class");
rc=lkup.definedone();
rc=lkup.find();
if rc eq 0 then return(Value_To_Return);
else return(.);
endsub;
quit;
options cmplib=work.functions;
data want;
set have;
returned_value = get_score(county, town, school, class);
run; Any suggestions on how to include a clause within the hash lookup to search for eventdate being between start_date and end_date?
... View more
