time data selection

JinboZhao — Thu, 06 Jul 2017 13:28:01 GMT

Hi SAS,

I have a dataset contains PERIOD_DT, START_DT, END_DT and other variables and millions of observations, like below:

ID	START_DT	END_DT	GRADE	QUALITY	PERIOD
1	31 January 2008	28 February 2008	A	E	31 May 2008
1	29 February 2008	30 March 2008	V	C	31 May 2008
1	31 March 2008	30 October 2008	D	D	31 May 2008
1	31 October 2008	27 November 2008	D	D	31 May 2008
1	28 November 2008	29 November 2009	E	E	31 May 2008
1	30 November 2009	29 April 2012	S	T	31 May 2008
2	31 January 2008	30 March 2008	B	B	31 May 2008
2	31 March 2008	30 July 2008	A	R	31 May 2008
2	31 July 2008	28 August 2008	V	V	31 May 2008
2	29 August 2008	30 October 2008	D	D	31 May 2008
2	31 October 2008	30 December 2008	D	Y	31 May 2008
2	31 December 2008	29 January 2009	E	E	31 May 2008
2	30 January 2009	29 April 2009	S	A	31 May 2008
2	30 April 2009	29 September 2009	B	S	31 May 2008

One ID has many none overlap START and END records. I want to get a new table which only contains the observations which the PERIOD time is within the START and END period.

According to above sample, I want to get a table like:

ID	START_DT	END_DT	GRADE	QUALITY	PERIOD
1	31 March 2008	30 October 2008	D	D	31 May 2008
2	31 March 2008	30 July 2008	A	R	31 May 2008

Many appreciation for your help.

Re: time data selection

art297 — Thu, 06 Jul 2017 13:39:02 GMT

data have;
  infile cards dlm='09'x;
  informat START_DT	END_DT	PERIOD anydtdte16.;
  format START_DT	END_DT	PERIOD date9.;
  input ID	START_DT	END_DT	(GRADE	QUALITY) ($)	PERIOD;
  cards;
1	31 January 2008	28 February 2008	A	E	31 May 2008
1	29 February 2008	30 March 2008	V	C	31 May 2008
1	31 March 2008	30 October 2008	D	D	31 May 2008
1	31 October 2008	27 November 2008	D	D	31 May 2008
1	28 November 2008	29 November 2009	E	E	31 May 2008
1	30 November 2009	29 April 2012	S	T	31 May 2008
2	31 January 2008	30 March 2008	B	B	31 May 2008
2	31 March 2008	30 July 2008	A	R	31 May 2008
2	31 July 2008	28 August 2008	V	V	31 May 2008
2	29 August 2008	30 October 2008	D	D	31 May 2008
2	31 October 2008	30 December 2008	D	Y	31 May 2008
2	31 December 2008	29 January 2009	E	E	31 May 2008
2	30 January 2009	29 April 2009	S	A	31 May 2008
2	30 April 2009	29 September 2009	B	S	31 May 2008
;

data want;
  set have (where=(period between start_dt and end_dt));
run;

Art, CEO, AnalystFinder.com

topic time data selection in SAS Data Management

time data selection

Re: time data selection