SAS Data step - In current dataset, loop through another dataset to get value

Question

I have 2 datasets like this

Dataset 1:

From       To           Period
01/1/2000  20/1/2000    1
21/1/2000  14/2/2000    2
15/2/2000  31/3/2000    3

Dataset 2:

Date
15/1/2000
13/2/2000
20/3/2000

And the desired result would be like this:

Date        Period
15/1/2000   1
13/2/2000   2
20/3/2000   3

I think the solution for this is going through dataset 2 by each record, look up and compare the dataset 2 date value with the range From To in Dataset 1 until a match is found, then get the Period value. Are we able to do this with SAS datastep code not sql? And if we do, the performance wise, would it be better?

Thanks for your help in advance, really appreciate it.

Answer 1

Creating an informat from your lookup dataset is the way to go here. You then use the informat to create the value with the INPUT function.

data ds1;
input From :ddmmyy10.  To :ddmmyy10. Period;
format From :ddmmyy10.  To :ddmmyy10.;
datalines;
01/1/2000  20/1/2000    1
21/1/2000  14/2/2000    2
15/2/2000  31/3/2000    3
;
run;

data ds2;
input date :ddmmyy10.;
format date ddmmyy10.;
datalines;
15/1/2000
13/2/2000
20/3/2000
;
run;

/* create dataset with informat details*/
data ds1_fmt;
set ds1;
rename from=start to=end period=label;
retain fmtname 'dt_fmt' type 'I';
run;

/* create informat from dataset */
proc format cntlin=ds1_fmt;
run;

/* create output dataset */
data want;
set ds2;
period=input(date,dt_fmt.);
run;