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

0
votes

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.

2
sasdatastep

2 Answers

1
votes

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;
0
votes
data lookup;
INFILE DATALINES;
input @1 from mmddyy10. @12 to mmddyy10. @22 period $;
datalines;                      
01/01/2000 01/20/2000 1
01/21/2000 02/14/2000 2
02/15/2000 03/31/2000 3
;
data base;
infile datalines; 
input @1 date mmddyy10.;
datalines;
01/15/2000
02/13/2000
03/20/2000
;

Try it with a proc sql

proc sql;
create table result as
select put(b.date,mmddyy10.), (select max(period) from lookup l where b.date between l.from and l.to) as period
from base b;
quit;