Create a Dynamic Date Variable

[KDG]

The reason you didn't get any data is because the code creates a "null" dataset. 

Try this instead: 

data test;
    enddate=today();
    startdate=intnx('day',enddate,-60);
    format enddate startdate mmddyyd10.;
run; 

[KDG]

format enddate startdate yymmddd10.;

[aperansi]

This is what I used, and I still dont receive any data back. 

data test;
    enddate=today();
    startdate=intnx('day',enddate,-60);    
	format enddate startdate yymmddd10.;
;run; 

I also tried yymmdd10. 

[KDG]

That code will create a dataset called Test that has the 2 variables in it. Are you using EG or SAS Studio? Try putting 

proc print; run; 

Otherwise, look in your log and see if it says the dataset was created. If it was, it will be in your WORK library.

[aperansi]

I am using SAS EG yes. I tried puting proc print; run; right after the intial code and I am still getting the same result unfortunately. 

The datasets are being created, but are returning with 0 rows of data in the work library. 

Here is a piece of what I am seeing in the log:

GOPTIONS ACCESSIBLE;
23         proc sql;
24         CONNECT TO odbc as myconn
24       ! (noprompt=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX);
25         
26         CREATE TABLE TUnionLD AS
27             select *
28             from connection to myconn
29              (
30                    SELECT  AD.AGENCY_DATA_KEY AS api_call,
31                            AD.DATE_ENTERED AS api_datetime
32                    FROM CUSTOMERSCORING.DBO.AGENCYDATA AD
33                    WHERE AD.AGENCY_MASTER_KEY in (68) AND AD.DATA_ERROR = 0 AND
34                         AD.DATE_ENTERED
SYMBOLGEN:  Macro variable STARTDATE resolves to 21455
34       !                                 >= &startdate. AND AD.DATE_ENTERED < &enddate.
SYMBOLGEN:  Macro variable ENDDATE resolves to 21515
35         
36                         )
37              ;
NOTE: Table WORK.TUNIONLD created, with 0 rows and 2 columns.

38         disconnect from myconn;
39         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.07 seconds
      cpu time            0.01 seconds

[Tom]

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.

[aperansi]

Thank you for helping me understand a little better Tom. 

I accessing a SQL server to perform my initial query as that is where the data originates from. 

Here is the example query with hard coded dates. I apolgoize I did not mention this sooner.

proc sql;
CONNECT TO odbc as myconn (noprompt="server=canldsassqlrpt;DRIVER=SQL Server;Trusted_Connection=yes;database=customerScoring");

CREATE TABLE TUnionLD AS 
    select *
    from connection to myconn
     (
           SELECT  AD.AGENCY_DATA_KEY AS api_call,
                   AD.DATE_ENTERED AS api_datetime
           FROM CUSTOMERSCORING.DBO.AGENCYDATA AD
           WHERE AD.AGENCY_MASTER_KEY in (68) AND AD.DATA_ERROR = 0 AND
                AD.DATE_ENTERED >= '2018-09-01' AND AD.DATE_ENTERED < '2018-11-01'

                )
     ;
disconnect from myconn;
quit;

[Tom · Accepted solution]

So you want to generate this statement:

select
   ad.agency_data_key as api_call
 , ad.date_entered as api_datetime
from customerscoring.dbo.agencydata ad
where ad.agency_master_key in (68)
  and ad.data_error = 0
  and ad.date_entered >= '2018-09-01'
  and ad.date_entered < '2018-11-01'

Replacing the dates with today and today - 60?

So first run this step to create macro variables.

data _null_;
  today=today();
  call symputx('enddate',quote(put(today,yymmddd10.),"'"));
  call symputx('startdate',quote(put(today-60,yymmddd10.),"'"));
run;

You can use %PUT to see if it worked.

27   %put &=enddate &=startdate;
ENDDATE='2018-11-27' STARTDATE='2018-09-28'

Now modify your other program and replace the hardcoded dates with references to the values of these new macro variables.

where ad.agency_master_key in (68)
  and ad.data_error = 0
  and ad.date_entered >= &startdate
  and ad.date_entered < &enddate

If you want the dates to fall on the first of the month then make that initial data step that is creating the macro variable more complicated.

data _null_;
  end=intnx('month',today(),0,'b');
  start=intnx('month',end,-2,'b');
  call symputx('enddate',quote(put(end,yymmddd10.),"'"));
  call symputx('startdate',quote(put(start,yymmddd10.),"'"));
run;

[aperansi]

Tom, that worked for a majority of the queries I need and thank you for your help with this. 

I am now encountering another issue however. I have a few queries in which the date entered works with the following:

'01oct2018:00:00:00'dt

here is an example of the working query:

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
		where ad.agency_master_key = 86 and ad.date_entered >= '01oct2018:00:00:00'dt and ad.date_entered < '01nov2018:00:00:00'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;

can you tell me how I would create the macro variable that would allow me to run the query with dates similar to the hard coded ones above?

[Tom]

So those are no longer DATE values (what day of the year is it) but DATETIME (or what databases call TIMESTAMP) values.

Your SQL Server database is not going to understand SAS's convention for datetime constants.  You need to look at what syntax your server excepts but it is probably something like:  '2018-10-01 00:00:00'

If you only need to generate midnight as the time part of the DATETIME value then just hard code it.

data _null_;
  end = today();
  call symputx('end_datetime',quote(put(end,yymmddd10.)||' 00:00:00'),"'");
run;

If you really have a SAS datetime value that you want to generate in that format then you could use something like:

data _null_;
  end = datetime();
  call symputx('end_datetime',quote(catx(' ',put(datepart(end),yymmddd10.),put(timepart(end),time8.))),"'");
run;

[PaigeMiller]

---

@aperansi wrote:

Tom, that worked for a majority of the queries I need and thank you for your help with this. 

I am now encountering another issue however. I have a few queries in which the date entered works with the following:

'01oct2018:00:00:00'dt

here is an example of the working query:

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
		where ad.agency_master_key = 86 and ad.date_entered >= '01oct2018:00:00:00'dt and ad.date_entered < '01nov2018:00:00:00'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;

can you tell me how I would create the macro variable that would allow me to run the query with dates similar to the hard coded ones above?

---

so the previous code used SAS date values, and now you want SAS date/time values.

data _null_;
  today=dhms(today(),0,0,0);
  call symputx('enddate',cats(quote(put(today,datetime16.),"'"),'dt'));
  startdate=intnx('dtday',today,-60,'b');
  call symputx('startdate',cats(quote(put(startdate,datetime16.),"'"),'dt'));
run;

%put &=enddate &=startdate;

[aperansi]

I made a slight edit to create new macro variables since I have different needs throughout the query, but I wanted your thoughts:

Here is the code:

data _null_;
  today=dhms(today(),0,0,0);
  call symputx('end_dt',cats(quote(put(today,datetime16.),"'"),'dt'));
  start_dt=intnx('dtday',today,-60,'b');
  call symputx('start_dt',cats(quote(put(start_dt,datetime16.),"'"),'dt'));
run;

Can you tell me what you think?

[Tom]

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.)

[aperansi]

When I ran that in SAS it returned back the information that I needed it to. 

I'm however not sure what you meant by it wont generate SQL Server code. I used the start_dt and end_dt between the date entered statement. Am i doing something wrong here?

[Tom]

The code you have posted before was using what is called explicit pass-through SQL.

CREATE TABLE TUnionLD AS
select * from connection to myconn
( ... )
;

the code inside those outer parentheses are passed to the remote database to execute.  So it needs to be SQL code that is valid IN THAT REMOTE DATABASE.  Unless your ODBC connection is to a SAS server then I really doubt that it knows how to interpret those SAS style datetime literals.

The later code you posted included the CONNECT and DISCONNECT statements as if you were going also query the remote database, but instead you just used a pure SAS code query.  That is why the SAS style datetime literals worked.  You are not actually sending the code directly to the database.  

:

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
  where ad.agency_master_key = 86
    and ad.date_entered >= '01oct2018:00:00:00'dt
    and ad.date_entered < '01nov2018:00:00:00'dt
    and xref.score_summary_key =
       (select min(score_summary_key)
        from csus.scoresummaryagencydataxref
        where agency_data_key = ad.agency_data_key
      )
;

 Although I would be surprised if that code worked since it is referencing table aliases of LOC and AD that are not defined in the query.