Create a Dynamic Date Variable

[aperansi]

I am looking to create a date variable that grabs the current date, and 60 days before the current date. Currently, I am using the following code:

%let startdate = '2018-09-23';
%let enddate = '2018-11-01';

%let start_dt = '23sep2018:00:00:00'dt;
%let end_dt = '01nov2018:00:00:00'dt; 

Can someone please help me figure out how to do this so that every month I dont have to manually update the dates? I need the dates in the format that I have specified above as well because I am running the initial query using a PROC SQL statement.

[PaigeMiller]

Okay, so I hope you realize that your example has more than 60 days between startdate and enddate. 

Also, if you want macro variable to contain date formatted as 2018-11-01, you are making your life hard. It could contain 01NOV18, which would be much easier to work with.

So, assuming you have enddate and you want to compute 60 days earlier

data _null_;
    enddate='01NOV18'd;
    startdate=intnx('day',enddate,-60);
run;

you can turn these into macro variables using CALL SYMPUTX

data _null_;
    enddate='01NOV18'd;
    call symputx('enddate',enddate);
    startdate=intnx('day',enddate,-60);
    call symputx('startdate',startdate);
run;

 Your SQL does not require formatted macro variables, it simply requires the actual SAS date (which is an integer, which is what the above produces) unless you are doing a passthru to some database where SAS dates won't work.

proc sql;
    select ... from ... where datevariable>=&startdate and datevariable<=&enddate;
quit;

[aperansi]

I do realize that part. I used the old variables for my example so they werent update with the correct 60 day period. I will try what you have mentioned above and see what my outcome is.

[KDG]

Also, you mention that you wanted to find the date 60 days before today. You can use the today function to dynamically get today's date so you don't have to hard code it in each time. 

data _null_;
    enddate=today();
    startdate=intnx('day',enddate,-60);
run;

[aperansi]

I used the following and no data returned back in my data sets unfortunately.  

Also, I need to calculate todays date without me manually having to input the date. Is this possible?

data _null_;
    enddate='01NOV18'd;
    startdate=intnx('day',enddate,-60);
    start_dt = '01NOV18'd;
    end_dt = intnx('day',enddate,-60);
run;

[PaigeMiller]

---

@aperansi wrote:

I used the following and no data returned back in my data sets unfortunately.  

Also, I need to calculate todays date without me manually having to input the date. Is this possible?

data _null_;
    enddate='01NOV18'd;
    startdate=intnx('day',enddate,-60);
    start_dt = '01NOV18'd;
    end_dt = intnx('day',enddate,-60);
run;

---

Yes, you have changed the code that I provided and so now you are getting nothing. If you run the code that I provided that contains CALL SYMPUTX, you get macro variables. You never get data set variables this way.

[aperansi]

I went back to the code you mentioned and used it, only making a slight adjustment to the start date.

data _null_;
    enddate=today();
    call symputx('enddate',enddate);
    startdate=intnx('day',enddate,-60);
    call symputx('startdate',startdate);
run;

I am still getting no data back in the datasets however.  I am still very confused on using macro variables however. I used the symputx like you mentioned, so are you implying that I wouldnt receive anything back in the datasets I need?

[PaigeMiller]

---

@aperansi wrote:

I went back to the code you mentioned and used it, only making a slight adjustment to the start date.

data _null_;
    enddate=today();
    call symputx('enddate',enddate);
    startdate=intnx('day',enddate,-60);
    call symputx('startdate',startdate);
run;

I am still getting no data back in the datasets however.  I am still very confused on using macro variables however. I used the symputx like you mentioned, so are you implying that I wouldnt receive anything back in the datasets I need?

---

The code creates macro variables only. It does not create data set variables.

After you run the data step, use

%put &=enddate;
%put &=startdate;

to see the values of your macro variables. The results are written to the LOG. They will be integers. You could also use

%put ENDDATE %sysfunc(putn(&enddate,date7.));
%put STARTDATE %sysfunc(putn(&startdate,date7.));

to see the actual dates that they represent.

[aperansi]

I did what you said, and used the following but still nothing is returning.

%put &=enddate;
%put &=startdate

I apologize for all of the questions, but I am very new and still dont have a very good understaning of SAS.

[PaigeMiller]

---

@aperansi wrote:

I did what you said, and used the following but still nothing is returning.

%put &=enddate;
%put &=startdate

I apologize for all of the questions, but I am very new and still dont have a very good understaning of SAS.

---

Did you look in the LOG?

[aperansi]

Yes I did look at the log, but not really sure what I'm looking for exactly. Nothing really sticks out to me. 

   
50         data _null_;
51             enddate=today();
52             call symputx('enddate',enddate);
53             startdate=intnx('day',enddate,-60);
54             call symputx('startdate',startdate);
55         	format endate startdate yymmdd10.;
56         run;

3                                                          The SAS System                            13:07 Monday, November 26, 2018

NOTE: Variable endate is uninitialized.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds
      

57         
58         %put &=enddate;
SYMBOLGEN:  Macro variable ENDDATE resolves to 21515
ENDDATE=21515
59         %put &=startdate;
SYMBOLGEN:  Macro variable STARTDATE resolves to 21455
STARTDATE=21455
60         
61         GOPTIONS NOACCESSIBLE;
62         %LET _CLIENTTASKLABEL=;
63         %LET _CLIENTPROJECTPATH=;
64         %LET _CLIENTPROJECTNAME=;
65         %LET _SASPROGRAMFILE=;
66         
67         ;*';*";*/;quit;run;
68         ODS _ALL_ CLOSE;
69         
70         
71         QUIT; RUN;
72         

[PaigeMiller]

It says 

ENDDATE=21515
STARTDATE=21455

which you will notice is exactly 60 days apart. Then you can use these macro variables in your SQL, as I explained near the beginning of this thread.

[aperansi]

Thank you for clearing that up for me. I used the macro variables like you mentioned in my SQL, but the column name is saying invalid. 

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 >= startdate AND AD.DATE_ENTERED < enddate

                )
     ;
disconnect from myconn;
quit;

[PaigeMiller]

AD.DATE_ENTERED >= &startdate AND AD.DATE_ENTERED < &enddate

[aperansi]

Updated to the following and there were no results returned in the query, 

AD.DATE_ENTERED >= &startdate AND AD.DATE_ENTERED < &enddate