Create a Dynamic Date Variable

[aperansi]

You dont know how much I really appreciate your explanation! Thank you so much. 

The code that I was needing help with in this thread were date variables that I was using outside of the SQL queries. However, it was the SQL queries that were referencing those macro variables. 

Do you think that this approach will suffice for what I need?

Also, to eliminate confusion, here is the entire portion of the code including the aliases. I just wanted to save space so I eliminated that portion from the post. The below query does work.

proc sql;
CONNECT TO odbc as myconn (noprompt="server=ictsassqlrpt01;DRIVER=SQL Server;Trusted_Connection=yes;database=customerScoring");
create table MLAOnline as
(
	SELECT DISTINCT loc.IS_INTERNET AS Is_internet, ad.AGENCY_DATA_KEY AS agency_data_key, datepart(ad.date_entered) AS api_date
		FROM csus.ScoreSummary ss
		INNER JOIN csus.ScoreSummaryAgencyDataXref xref ON xref.SCORE_SUMMARY_KEY = ss.SCORE_SUMMARY_KEY
		INNER JOIN csus.AgencyData ad ON ad.AGENCY_DATA_KEY = xref.AGENCY_DATA_KEY
		LEFT JOIN csus.Location loc ON loc.LOCATION_KEY = ss.LOCATION_KEY
		where ad.agency_master_key = 86 and ad.date_entered >= &start_dt and ad.date_entered < &end_dt
		AND xref.SCORE_SUMMARY_KEY = (SELECT MIN(score_summary_key) FROM csus.ScoreSummaryAgencyDataXref WHERE AGENCY_DATA_KEY = ad.AGENCY_DATA_KEY)
		
		)
	;
disconnect from myconn;
quit;

[Tom]

Why do you include the CONNECT and DISCONNECT statements when your CREATE table statement is not using the connection you created?

[aperansi]

To be quite honest I am not sure. This was code created by someone else, and I am merely expanding on the project as a whole to make it more efficient. Hence why I posed the question in this thread. 

Do you believe that it is not necessary to run the Create table? 

[PaigeMiller]

---

@Tom wrote:

Your code generates macro variables (strings) that look like this:

160  %put &=end_dt &=start_dt ;
END_DT='27NOV18:00:00:00'dt START_DT='28SEP18:00:00:00'dt

Those strings will be useful for generating SAS code

proc print data=mylib.mydata ;
  where my_datetime_variable between &start_dt and &end_dt;
run;

, but not for generating SQL server code.

Also, you might want to use a width of 19 instead of 16 so that you generate 4 digit years. (There is a "feature" of the DATETIME. format and a width of 18 still uses only two digit years even though there is room for four digits.)

---

When I use datetime19, I get a four digit year, but a space after the initial quote, which doesn't seem right for the intended use, but I admit I haven't tried the datetime19. with the blank in an actual SQL call.

END_DT=' 28NOV2018:00:00:00'dt START_DT=' 29SEP2018:00:00:00'dt

[Tom]

The posted code was using CATS() function that would remove that leading space. You can also just use an extra assignment statement to remove leading spaces.

[PaigeMiller]

---

@Tom wrote:
The posted code was using CATS() function that would remove that leading space. You can also just use an extra assignment statement to remove leading spaces.

---

CATS does not remove the space when I run it.

[Tom]

Check your order of operations?

228  data _null_;
229   call symputx('mvar',cats("'",put(datetime(),datetime19.),"'dt"));
230  run;

NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


231  %put &=mvar;
MVAR='28NOV2018:09:42:05'dt
232
233
234  %let mvar=%sysfunc(datetime(),datetime19);
235  %let mvar="&mvar"dt;
236  %put &=mvar;
MVAR="28NOV2018:09:45:16"dt

[aperansi]

The code runs just fine so I'm not sure what I'm looking for here. Why would I need to check the order of operation?

[Tom]

---

@aperansi wrote:
The code runs just fine so I'm not sure what I'm looking for here. Why would I need to check the order of operation?

---

That is in response to the question of how to use DATETIME19. format and eliminate the leading space it generates. The CATS() function or using a %LET statement to assign the macro variable value to another macro variable (or even to recreate the same macro variable) will remove the leading space before you add the quotes and the dt suffix. 

But if you add the quotes first then those methods of removing leading spaces will not work since the spaces are no longer the leading characters. The quote is.

To your other question about the extra CONNECT statement.  It does not really matter if you keep it.  It is just confusing because it looked like you were going to use pass through SQL, which would have meant that you would need to use a different format for your datetime (timestamp) constants.  The only trouble the extra CONNECT statement does is it could make your program fail if the connection to ODBC cannot be made which would be a shame because you don't really need the connection for the query you are actually running. 

[aperansi]

How would I use an assignment statement to remove the leading spaces?

[PaigeMiller]

---

@Tom wrote:

On the 13th message in this thread you just introduced a critical piece of information that you should have include in your initial report.  You are trying to use these "date" values in some remote database instead of in SAS code.

If you want help with generating valid code for that remote database you need to explain what syntax will work.

So get an example of the query that works using hard-coded dates and show us that code so we can help you replace the hardcoded dates that will be in the right syntax for that remote database to understand.

---

Nice catch, Tom!