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.
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
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.