Help using Base SAS procedures

How to create a macro to filter dt

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 80
Accepted Solution

How to create a macro to filter dt

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?

Accepted Solutions
Solution
‎05-11-2016 12:23 AM
Super User
Posts: 10,550

Re: How to create a macro to filter dt

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.

View solution in original post


All Replies
Frequent Contributor
Posts: 80

Re: How to create a macro to filter dt

Fyi here is example of data 09may2016:12:33:09.000000
Regular Contributor
Posts: 212

Re: How to create a macro to filter dt

Hi mate.

 

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

Is this what you need ?

 

Hope this helps

Solution
‎05-11-2016 12:23 AM
Super User
Posts: 10,550

Re: How to create a macro to filter dt

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.

Frequent Contributor
Posts: 80

Re: How to create a macro to filter dt

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
Super User
Posts: 10,550

Re: How to create a macro to filter dt

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.

 

 

Frequent Contributor
Posts: 80

Re: How to create a macro to filter dt

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 .

Super User
Posts: 5,099

Re: How to create a macro to filter dt

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.

Super User
Posts: 10,550

Re: How to create a macro to filter dt

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 463 views
  • 2 likes
  • 4 in conversation