Managing Dates from a Loop and Using them in a Macro Running SQL

[chrishull]

Hi All,

I've been stubling through this issue for a while and can't seem to resolve managing my date formatting for my sql. I'm trying to loop the same SQL for 16 time periods and output the results. At the moment, I'm expecting the results to output to 16 reports but it would be nice to have the results from each loop inserted into a table with a label of the year from date1. Here's what I have cobbled together so far:

data want;
do i=1 to 16;
	date1=put(intnx('year', "01Apr2000"d,i,'sameday'),date9.);
	date2=put(intnx('year', "31Mar2001"d,i,'sameday'),date9.);
	call symputx('date1',date1);
	call symputx('date2',date2);
	str=catt('%multiple_runs(date1=',date1, ', date2=',date2,');');
	call execute(str);
	output;
end;
run;

%macro multiple_runs(date1=, date2=);
	PROC SQL;
	SELECT CLASS_CD, COUNT(DISTINCT ID) AS DISTINCT_ID 
		FROM TABLE1
		WHERE
			CLASS_CD  = 'AB' and 
			EFFECTIVE_DATE  <= "&date1"d and 
			((END_DATE  IS NULL ) or (END_DATE  >= "&date2"d)) and 
			REPLACED_TMESTMP  IS NULL
		GROUP BY CLASS_CD;
%mend;

The dates appear to be outputting correctly from the data step. I just can't seem to format them correctly for the SQL... Here's the data output:

i	date1	        date2	        str
1	01APR2001	31MAR2002	%multiple_runs(date1=01APR2001, date2=31MAR2002);
2	01APR2002	31MAR2003	%multiple_runs(date1=01APR2002, date2=31MAR2003);
3	01APR2003	31MAR2004	%multiple_runs(date1=01APR2003, date2=31MAR2004);
4	01APR2004	31MAR2005	%multiple_runs(date1=01APR2004, date2=31MAR2005);
5	01APR2005	31MAR2006	%multiple_runs(date1=01APR2005, date2=31MAR2006);
6	01APR2006	31MAR2007	%multiple_runs(date1=01APR2006, date2=31MAR2007);
7	01APR2007	31MAR2008	%multiple_runs(date1=01APR2007, date2=31MAR2008);
8	01APR2008	31MAR2009	%multiple_runs(date1=01APR2008, date2=31MAR2009);
9	01APR2009	31MAR2010	%multiple_runs(date1=01APR2009, date2=31MAR2010);
10	01APR2010	31MAR2011	%multiple_runs(date1=01APR2010, date2=31MAR2011);
11	01APR2011	31MAR2012	%multiple_runs(date1=01APR2011, date2=31MAR2012);
12	01APR2012	31MAR2013	%multiple_runs(date1=01APR2012, date2=31MAR2013);
13	01APR2013	31MAR2014	%multiple_runs(date1=01APR2013, date2=31MAR2014);
14	01APR2014	31MAR2015	%multiple_runs(date1=01APR2014, date2=31MAR2015);
15	01APR2015	31MAR2016	%multiple_runs(date1=01APR2015, date2=31MAR2016);
16	01APR2016	31MAR2017	%multiple_runs(date1=01APR2016, date2=31MAR2017);

I appreciate any suggestions!

Thanks in advance

[ballardw]

Save yourself a bunch of work and don't bother with formatted dates and date literals. Formats are basically for human consumption.

And since you are only using the values in CALL Execute them pass them directly the macro variable isn't needed at all.

Consider:

%macro multiple_runs(date1=, date2=);
	PROC SQL;
	SELECT CLASS_CD, COUNT(DISTINCT ID) AS DISTINCT_ID 
		FROM TABLE1
		WHERE
			CLASS_CD  = 'AB' and 
			EFFECTIVE_DATE  <= &date1 and 
			((END_DATE  IS NULL ) or (END_DATE  >= &date2)) and 
			REPLACED_TMESTMP  IS NULL
		GROUP BY CLASS_CD;
        Quit;
%mend;

data want;
do i=1 to 16;
	date1=intnx('year', "01Apr2000"d,i,'sameday');
	date2=intnx('year', "31Mar2001"d,i,'sameday');
	str=catt('%multiple_runs(date1=',date1, ', date2=',date2,');');
	call execute(str);
	output;
end;
run;

[chrishull]

Thanks for the quick response. I initialy thought the same thing as you. However, I'm running into an issue though were the dates aren't resolving. I'm assuming it's related to the Oracle Exadata server. The code you supplied does work but the output results are incorrect. I should have counts >20,000 but i'm getting 19 for each of the 16 runs.

I had assumed that I needed to format my date in SQL to the oracle tables to date/time. Maybe I'm incorrect?

[ballardw]

---

@chrishull wrote:

Thanks for the quick response. I initialy thought the same thing as you. However, I'm running into an issue though were the dates aren't resolving. I'm assuming it's related to the Oracle Exadata server. The code you supplied does work but the output results are incorrect. I should have counts >20,000 but i'm getting 19 for each of the 16 runs.

I had assumed that I needed to format my date in SQL to the oracle tables to date/time. Maybe I'm incorrect?

---

You did not mention that you were sending this to another DBMS. That will be a crucial piece of information in general as pretty much every one of them uses different date conventions.

[chrishull]

You're right. Sorry about that. It's new here and I don't have much experience. Any suggestions on using modifying the date format for the oracle environment?

[Tom]

I would still recommend modifying the macro to take actual date values and add the Oracle intellegence into the macro.

Here is a page that recommends using ANSI standard DATE literals with Oracle. http://rdbms-insight.com/wp/?p=179

WHERE CLASS_CD = 'AB'
  and EFFECTIVE_DATE <= DATE %sysfunc(dequote("'%sysfunc(putn(&date1,yymmdd10))'"))
  and ((END_DATE IS NULL )
    or (END_DATE >=  DATE %sysfunc(dequote("'%sysfunc(putn(&date2,yymmdd10))'"))))
  and REPLACED_TMESTMP IS NULL

[ballardw]

Can you show the code of one of the queries that worked before introducing any macro elements that involved a date?

Sometimes these things involve the order of quotation marks single and double and sometimes nesting them

[chrishull]

When I supply the dates directly, I don't have any issues. Here's the code for just the query:

PROC SQL;
SELECT CLASS_CD, COUNT(DISTINCT ID) AS DISTINCT_ID 
	FROM TABLE1
	WHERE
		CLASS_CD  = 'AB' and 
		EFFECTIVE_DATE  <= "01Apr2000"d and 
		((END_DATE  IS NULL ) or (END_DATE  >= "31Mar2001"d)) and 
		REPLACED_TMESTMP  IS NULL
	GROUP BY CLASS_CD;
QUIT;

[Tom]

Your report is not what that code would generate.  The CAT...() functions do not honor the formats.

So your STR variable is going to look like:

date1=01APR2001
date2=31MAR2002
str=%multiple_runs(date1=15066,date2=15430);

You can either change your macro to accept actual dates.

WHERE CLASS_CD  = 'AB'
  and EFFECTIVE_DATE  <= &date1
  and ((END_DATE  IS NULL ) or (END_DATE  >= &date2))
  and REPLACED_TMESTMP  IS NULL

You could still call it manually by passing in date literals if you want.

%multiple_runs(date1='01APR2001'd,date2='31MAR2002'd);

OR you could change the data step to use VVALUE() function so that the formatted values are used when generating the command string.

str=cats('%nrstr(%multiple_runs)(date1=',vvalue(date1),',date2=',vvalue(date2),');');

Note that I also wrapped the macro name in %NRSTR() so that the call is pushed onto the execution stack instead of the code that the macro generates.  I find this eliminates timing issues and also cuts down on the number of characters SAS has to stack up and makes the SAS log easier to read.