topic Re: Create a Dynamic Date Variable in New SAS User

Create a Dynamic Date Variable

aperansi — Tue, 27 Nov 2018 16:04:07 GMT

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.

Re: Create a Dynamic Date Variable

PaigeMiller — Tue, 27 Nov 2018 16:15:22 GMT

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;

Re: Create a Dynamic Date Variable

aperansi — Tue, 27 Nov 2018 16:14:46 GMT

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.

Re: Create a Dynamic Date Variable

aperansi — Tue, 27 Nov 2018 16:21:22 GMT

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;

Re: Create a Dynamic Date Variable

KDG — Tue, 27 Nov 2018 16:22:32 GMT

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;

Re: Create a Dynamic Date Variable

PaigeMiller — Tue, 27 Nov 2018 16:25:38 GMT

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

Re: Create a Dynamic Date Variable

KDG — Tue, 27 Nov 2018 16:29:41 GMT

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;

Re: Create a Dynamic Date Variable

KDG — Tue, 27 Nov 2018 16:31:01 GMT

format enddate startdate yymmddd10.;

Re: Create a Dynamic Date Variable

aperansi — Tue, 27 Nov 2018 16:33:29 GMT

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?

Re: Create a Dynamic Date Variable

aperansi — Tue, 27 Nov 2018 16:38:06 GMT

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.

Re: Create a Dynamic Date Variable

PaigeMiller — Tue, 27 Nov 2018 16:39:44 GMT

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

Re: Create a Dynamic Date Variable

KDG — Tue, 27 Nov 2018 16:45:56 GMT

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.

Re: Create a Dynamic Date Variable

aperansi — Tue, 27 Nov 2018 16:51:48 GMT

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

Re: Create a Dynamic Date Variable

aperansi — Tue, 27 Nov 2018 16:59:33 GMT

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.

Re: Create a Dynamic Date Variable

PaigeMiller — Tue, 27 Nov 2018 17:46:34 GMT

@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?

Re: Create a Dynamic Date Variable

aperansi — Tue, 27 Nov 2018 17:50:36 GMT

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

Re: Create a Dynamic Date Variable

PaigeMiller — Tue, 27 Nov 2018 17:54:46 GMT

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.

Re: Create a Dynamic Date Variable

aperansi — Tue, 27 Nov 2018 18:01:28 GMT

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;

Re: Create a Dynamic Date Variable

PaigeMiller — Tue, 27 Nov 2018 18:03:36 GMT

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

Re: Create a Dynamic Date Variable

Tom — Tue, 27 Nov 2018 18:07:32 GMT

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.