How to create a macro to filter dt

[Beto16]

Hi I have a macro
Data _null_;start _ date = today ();;
Call symput ('dt' , " ' "||put(start_date-1 ,yymmdd10.)||" ' ");

Call symput ('dt', " ' "||put (end _ date -1,yymmdd10.)||" ' ");

It doesn't bring any info because the data that I'm pulling from is a datetime 25.6


How do I convert my macro to pick up the datetime25.6 format?

[Beto16]

Fyi here is example of data 09may2016:12:33:09.000000

[DartRodrigo]

Hi mate.

%let tdate = %sysfunc(today(),datetime25.6);
%put &tdate.;

Is this what you need ?

Hope this helps

[ballardw · Accepted solution]

1) Start_date is a date value. If you want a DATETIME value you need to make one. Easiest is

DateTimeValue = DHMS(today(),0,0,0);  to make one with zero for hour, minute and second.

If you want it for the day before

DateTimeValue = DHMS(today() -1,0,0,0);  (or use INTNX function)

To get a macro variable with a DATETime appearance then you need to specify a DATETIME format with PUT. Since you know that you want datetime25.6

Call symput('dt',put(Datetimevalue,datetime15.6));

Some other things, If you use Call Symput('dt', ..) twice as shown only the last value will be kept. You need separate macrovariable names, perhaps dtstart and dtend.

Thing very carefully about using quotes in the actual definition of a macro variable. Also HOW exactly will you use the resulting macrovariables? If for titles or text you would not want the quotes. If for comparisons with other datetime valued variables you don't need to use a datetime format as the numeric value would work.

Your comment: It doesn't bring any info because the data that I'm pulling from is a datetime 25.6

doesn't have any meaning in the context presented. You are assuming we know a lot more than has been shown.

[Beto16]

Your right sorry about that I tried your code ...the macro example it should be dt and dt1 I used as a between state. ...I'm getting an warning apparent invocation of macro the not resolved

Data _null_;start _ day =today ();
Call symput ('dt', " ' "|| put (start_date-1,datetime15.6));


Prod date = &dt. When code runs it outputs 01jan60:06:43

this is what data on table looks like

09may2016:12:33:09.000000

[ballardw]

Please show the code actually used to generate the macro value AND the code where you attempt to use the variable and the log.

And did you pay attention to the use of DHMS to create an actual DATETIME type variable in the code I posted previously? 

Your example code using TODAY() generates a DATE, not a DATETIME. Since SAS stores dates as number of days since 1 Jan 1960 and DATETIME as number of SECONDS there are significant differences of the results when you treat a date, as in your example, as if it were a datetime.

[Beto16]

I'm now using
Data _null_;
Start_date= today ();
Call symput ('dt' , " ' "||put (dhms (start_date-1,0,0,0),datetime.)||" 'dt"

Call symput ('dt1' , " ' "||put (dhms (start_date-1,0,0,0),datetime.)||" 'dt");
run;

where open _date between dt. And dt1.


Here is error message
Teradata odbc teradata driver Tera data database syntax error ,expected something like an UDFCALLNAME keyword '.' And the 'AND' the keyword .

[Astounding]

There's a standard remedy for this type of problem.  Get the program to work without using any macro language.  Once you figure out what Teradata is looking for, it won't be hard to turn that into a macro.  But you have to have a working program first.

[ballardw]

You still are not showing the code USING the macro variables. The error message looks incomplete. If the Error is in the log then copy the code and error from the log post it.