SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

time data selection

Accepted Solution Solved
Reply
Contributor
Posts: 34
Accepted Solution

time data selection

Hi SAS,

 

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

IDSTART_DTEND_DTGRADEQUALITYPERIOD
131 January 200828 February 2008AE31 May 2008
129 February 200830 March 2008VC31 May 2008
131 March 200830 October 2008DD31 May 2008
131 October 200827 November 2008DD31 May 2008
128 November 200829 November 2009EE31 May 2008
130 November 200929 April 2012ST31 May 2008
231 January 200830 March 2008BB31 May 2008
231 March 200830 July 2008AR31 May 2008
231 July 200828 August 2008VV31 May 2008
229 August 200830 October 2008DD31 May 2008
231 October 200830 December 2008DY31 May 2008
231 December 200829 January 2009EE31 May 2008
230 January 200929 April 2009SA31 May 2008
230 April 200929 September 2009BS

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:

IDSTART_DTEND_DTGRADEQUALITYPERIOD
131 March 200830 October 2008DD31 May 2008
231 March 200830 July 2008AR31 May 2008

 

Many appreciation for your help. 

 


Accepted Solutions
Solution
‎07-06-2017 09:59 AM
PROC Star
Posts: 7,357

Re: time data selection

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

View solution in original post


All Replies
Solution
‎07-06-2017 09:59 AM
PROC Star
Posts: 7,357

Re: time data selection

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 164 views
  • 0 likes
  • 2 in conversation