Automating date ranges

[vichyroc1]

I would appreciate any suggestions on the proc sql below. I have shortened the query but basically I have 4 instances of case statements in this query where the between date ranges are repeated and I would like to automate the dates in a way where they constantly change within a set parameter and save different output files separately. For example if the d.origi_dt BETWEEN DATE '2012-07-26' AND DATE '2012-07-31' then 1. copy these dates on all 4 case statements without manually changing them every time 2. but also change it to the next date range interval which is between '2012-08-01 and date '2012-08-05' and so on and so on. I know there is 'INTNX' variable but I am not too familiar on its application. If any SAS expert could lead me into the right direction or show me any code examples that i can replicate for my query on how to automate this script that would be great help as I have hit a roadblock here. 

options mlogic symbolgen;
 %let sasEngine = /user/local/sas; 
 libname here '/test/tmap/secmov'; 
 /* pull data*/ Proc SQL; 
 connect to oracle (database=); 
 create table here.abc as 
 select * from connection to oracle 
 	(SELECT c.rgi_nb, c.adr.nb, c.occ_nb, c.sty_dt, a.dpt_dt '-' as Duration, 
 		(Case When SUBSTRING (a.rgi_nb FROM 1 FOR 2) IN ('H', 'C') Then 'Other' 
 		When SUBSTRING (a. FROM 1 FOR 2) IN ('CN') Then 'Brand1' 
 		When SUBSTRING (a.Txt FROM 1 FOR 1) IN ('C','X') Then 'Brand2' 
 		Else 'Other' END) as BB1, 
 		(Case When SUBSTRING (a.base_Txt FROM 2 FOR 2) IN ('NZ') Then 'FO' 
 		Else 'Other' END) as GO, 
 		WHERE (d.origi_Dt BETWEEN DATE '2012-07-26' AND DATE '2012-07-31') 
 		WHERE ((d.origi_LDt BETWEEN DATE '2012-07-26' AND DATE '2012-07-31' 
 		AND WHERE ((d.origi_LDt BETWEEN DATE '2012-07-26' AND DATE '2012-07-31' 
 		AND WHERE ((d.origi_LDt BETWEEN DATE '2012-07-26' AND DATE '2012-07-31' 
 		AND quit;

[LinusH]

I'm no Oracle specialist, but the SQL doesn't look right. But I take it that's because you shortened the query, right?

You say you have 4 instances of "case statements", but what's visible in your query with dates, are where-statements - ok?

1. Use macro variables. I you are going to use them in Oracle pass-thru, be sure to format the dates properly. Also, include the quotation int the macro variable, it will be easier when you will resolve them.

2. Yes, there is a function called intnx. But I don't really follow you how you would use that in your situation, need to know more about how you want to define youir date intervals. But to use it when defining macro variables - you need to enclose it with the %sysfunc macro function.

Spontaneously there might be a more convenient way of solving your query, but since it's stripped/tilted, it's hard to give relevant advice. Is it three different queries (you have three identical where statements at the bottom).

[TomKari]

Try to give us the essence of what you're trying to do with the dates, and what kind of automation you're trying to accomplish. In normal language, no need to use SQL.

Tom

[vichyroc1]

In the SQL I have there is one between date range statement that occurs multiple times at different stages and where I was reaching out to the community to find if there is a way to either :-

1. automate all the date ranges as they both come from one single date column instead of changing them manually everytime. Since the date range in this SQL case is an 'issue date' where the issue date has to be in 5 date increments to be able to save different sets of data sets. eg issue_date between '2016-01-01' and '2016-01-05'. 

or

2. combine all the between date range statements into one and still automate the proc sql in a way that it will read one set of date range, run it save the results, run it dynamically for the second date range increment and so on.

thank you 

[LinusH]

"5 date increments" - means a 5 day range, or are you referring to 5 different ranges where '2016-01-01' and '2016-01-05' is one?

I still think that assigning macro variables will help you. How depends on the way you wish to specify them, describe your process.

The post is in the Stored Process forum, is this a Stored Process?

[vichyroc1]

You are correct, I meant 5 day range where '2016-01-01' and '2016-01-05' is one range  '2016-01-06' and '2016-01-10' is the next one and contunues until it hits the user defined limit.

I have attached a simplified version of the script for your reference.

Thank you

V

[Timmy2383]

I think I see what you're trying to do, but I'm a little confused by the fact that your first date range is 6 days and your second date range is 5 days.  Is that intentional? If so, then why?

So, assuming that your date ranges are meant to be 6 days, it looks to me like you want to be able to 1) set your starting date range and then 2) automatically generate any number of additional date ranges baed on the first one. Is this correct? If so, can you confirm that this code generates correct date ranges?

/*Set your starting date ranges*/
%LET STARTDATE = 2012-07-26;
%LET ENDDATE = 2012-07-31;
%LET NUMBER_OF_QUERIES=3;

/*Creating a dataset of date ranges for review.  We will change this later to just create macro variables*/
DATA DATE_RANGES;
	FORMAT DATE1 DATE2 YYMMDD10.;
	/*Determinig the difference between the initial date ranges so subsequent date ranges will do the same*/
	DIFFERENCE = (INPUT("&ENDDATE",YYMMDD10.) - INPUT("&STARTDATE",YYMMDD10.))+1;
	/*This will eventually be macro code instead, but using a data step DO loop output user-defined number of date ranges*/
	/*Just change value for NUMBER_OF_QUERIES above*/
	DO I = 0 TO &NUMBER_OF_QUERIES.-1;
		DATE1 = INPUT("&STARTDATE",YYMMDD10.)+SUM(I*DIFFERENCE);
		DATE2 = INPUT("&ENDDATE",YYMMDD10.)+SUM(I*DIFFERENCE);
		OUTPUT;
	END;

RUN;

All you have to do is define the starting date ranges and then the number of queries you want to run (at the top) and then the second step will generate the date ranges for the queries.  If this is doing want you want then we can further modify the code and incorporate your query.

[vichyroc1]

Thank you for replying.

Sorry about that confusion but the posted date range was arbitrary, to your point it should be 5 day range.

1. '2012-07-26' and '2012-07-30' 

2. '2012- 07-31' and '2012-08-04'

contd..........

When I ran your code I get the following

Date1           Date2          Difference  I

2012-07-26  2012-07-31  6               0
2012-08-01  2012-08-06  6               1
2012-08-07  2012-08-12  6               2

It gives me the expected date ranges back. Now as you might have looked at my script (attached here) what would be the best possible way to integrate the code you have written to give me all the data between each individual date range, as in my proc sql date column is defined as Ldt between date '2012-07-26' AND DATE '2012-07-30'?

Any pointers are appreciated.

Thanks 

V

[Timmy2383]

Try this:

/*Set your starting date ranges*/
%LET STARTDATE = 2012-07-26;
%LET ENDDATE = 2012-07-30;
%LET NUMBER_OF_QUERIES=3;

%MACRO PullData;

%DO I=0 %TO &NUMBER_OF_QUERIES. ;
	
	DATA _NULL_;
		/*Determinig the difference between the initial date ranges so subsequent date ranges will do the same*/
		DIFFERENCE = (INPUT("&ENDDATE",YYMMDD10.) - INPUT("&STARTDATE",YYMMDD10.))+1;
		/*Calculate the date values and create macro variables for the date ranges.*/
		CALL SYMPUTX("STARDATE1",PUT(INPUT("&STARTDATE",YYMMDD10.)+SUM(&I.*DIFFERENCE),YYMMDD10.));
		CALL SYMPUTX("ENDDATE1",PUT(INPUT("&ENDDATE",YYMMDD10.)+SUM(&I.*DIFFERENCE),YYMMDD10.));
	RUN;

	

Proc SQL;
  connect to teradata (database=);

create table here.new&I. as select * from connection to teradata
(
	SELECT 
	d.Nbr, 
	d.Sequence, 
	a.Nb, 
	'-' as Duration, 
	(Case When a.Dir = b.Dir Then 'S' Else 'M' END) as Type,  
	AL.Gphc_Adrress, 
	AL4.Gphc_Adr2, 
	FROM OP.Base a 
	LEFT OUTER JOIN MV.STN b ON 
	(a.Cd=b.Cd AND 
	a.Cd=b.Cd) 
	FROM OP.D5a, 
	MV.D5b 
	WHERE (Doc=Doc AND 
	D5.Ldt=LDt AND 
	D5.Sq=Sq)  AND 
	((D5a.Inav='N' AND 
	D5a.Ind='N' AND 
	(D5b.Cd='D' OR D5b.Op='D') 
	LEFT OUTER JOIN (
	SELECT DISTINCT 
	D.kt, 
	D.LDt, 
	D.Sq, 
	SUM ( D6.Dc ) 
	FROM OPMV.CHRG 
	WHERE (D6.LDt BETWEEN DATE %BQUOTE(')&STARTDATE1%BQUOTE(') AND DATE %BQUOTE(')&ENDDATE1%BQUOTE(')) 
	GROUP BY 
	D6.Doc_Nb, 
	D6.Doc_LDt, 
	D6.Sq) 
	AL(nbr, dt, sq, amt)  ON 
	(AL.nbr=AL.nbr 
	LEFT OUTER JOIN (
	SELECT 
	D7.Doc_Nb, 
	SUM ( D7b.Dc ) 
	FROM OPMV.TK
	LEFT OUTER JOIN OPMV.MISC ON 
	(D7a.Cur_RM=D7b.Rm_Nb 
	WHERE ((D7a.LDt BETWEEN DATE %BQUOTE(')&STARTDATE1%BQUOTE(') AND DATE %BQUOTE(')&ENDDATE1%BQUOTE(') 
	GROUP BY 
	D7a.Doc_Nb, 
	D7a.Iss_LDt, 
	ORDER BY 4,1,3
);

quit;



	
%END;


%MEND;

%PullData;

[vichyroc1]

I am getting the following error and I made sure all the quotations and parenthese were in place. 

3988 GOPTIONS XPIXELS=0 YPIXELS=0;
3989 FILENAME EGSR "&_eg_gridworkpath/%sysfunc(uuidgen(0))_eggen"
-
49
3989! ;
3990 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue
3990! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/x86/SASEnterpriseGuide/5.1/Styles/HtmlBlue.css"

----------------------------------------------------------------------------
49
3990! ) NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
2 The SAS System 09:29 Monday, February 29, 2016

between a quoted string and the succeeding identifier is recommended.
3991
3992 GOPTIONS ACCESSIBLE;
WARNING: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation
marks.

4022 DIFFERENCE = (INPUT("&ENDDATE",YYMMDD10.) -
4022! INPUT("&STARTDATE"
-
49
4022! ,YYMMDD10.))+1;
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.
4023 /*Calculate the date values and create macro variables for the date ranges.*/
4024 CALL
4024! SYMPUTX("STARDATE1",PUT(INPUT("&STARTDATE"
-
49
4024! ,YYMMDD10.)+SUM(&I.*DIFFERENCE),YYMMDD10.));

[Timmy2383]

I don't see the error message. Just a note.

[vichyroc1]

Are you able to see the error message in this one? Let me know

3988 GOPTIONS XPIXELS=0 YPIXELS=0;
3989 FILENAME EGSR "&_eg_gridworkpath/%sysfunc(uuidgen(0))_eggen"
-
49
3989! ;
3990 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue
3990! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/x86/SASEnterpriseGuide/5.1/Styles/HtmlBlue.css"
----------------------------------------------------------------------------
49
3990! ) NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on");
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
2 The SAS System 09:29 Monday, February 29, 2016
between a quoted string and the succeeding identifier is recommended.
3991
3992 GOPTIONS ACCESSIBLE;
WARNING: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation
marks.


4022 DIFFERENCE = (INPUT("&ENDDATE",YYMMDD10.) -
4022! INPUT("&STARTDATE"
-
49
4022! ,YYMMDD10.))+1;
NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended.
4023 /*Calculate the date values and create macro variables for the date ranges.*/
4024 CALL
4024! SYMPUTX("STARDATE1",PUT(INPUT("&STARTDATE"
-
49
4024! ,YYMMDD10.)+SUM(&I.*DIFFERENCE),YYMMDD10.));

[Timmy2383]

Nope, just a WARNING and a NOTE.

"WARNING: The quoted string currently being processed has become more than 262 characters long. You might have unbalanced quotation marks." 

This looks like it's coming from something in your environment, not my code.

"NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space
between a quoted string and the succeeding identifier is recommended."

Just a note, so shouldn't affect the functionality.

What am I missing?

[vichyroc1]

Let me verify if there is an environment issue on my end. And rerun the code and confirm with you asap if it pulls the data back.

Thanks