Desktop productivity for business analysts and programmers

Using macro variable to refer to Date.

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

Using macro variable to refer to Date.

Hello, 

 

I am writing a program to read data files, filter and clean, and return a set of data falling within a specified date range. My program works fine and is relatively simple. However, when I add macro variables for the date range, the code will not run. I believe it is due to the fact that single quotes surround the datetime value. I have run into this problem before but within the proc import command where the path name must be surrounded by single quotes. In this instance I opted to use a "let" statement and input the path with the quotes around in rather than put the quotes into the code itself. However, this process is a bit more cumbersome because the date values can be long and I'd rather not have to type in such long datetime values in order to run my code. 

 

Here's the final piece of code without the macro variable:

 

data work.Time_Sourced1;
set work.Time_Sourced;
where WEBU_Datetime >= '27Jun2016:6:00'DT and WEBU_Datetime <= '4July2016:5:59'DT;
run;

 

(For my purposes, days begin at 6am and end at 5:59am the next day... for this reason the "time" portions do not need to change from one set of data to the next)

 

Now when I place the macro this is what it looks like:

 

 

%myprogram(begin, end)

 

data work.Time_Sourced;
set work.Time_Sourced;
where WEBU_Datetime >= '&begin:6:00'DT and WEBU_Datetime <= '&end:5:59'DT;
run;

 

 

and this is the error I am receiving:

 

 

24 data work.Time_Sourced;
25 set work.Time_Sourced;
26 where WEBU_Datetime >= '&begin:6:00'DT and WEBU_Datetime <= '&end:5:59'DT;
ERROR: Invalid date/time/datetime constant '&begin:6:00'DT.
ERROR: Invalid date/time/datetime constant '&end:5:59'DT.
ERROR: Syntax error while parsing WHERE clause.
27 run;

 

 

A way around this may be to use let statements and construct the macro variable from within the code. Such as:

 

%myprogram(Date_Start,Date_End)

 

%let Date1 = &Date_Start;

%let Date2 = &Date_End;

%let time = :6:00'DT

 

%let begin= catt(',&Date_Start,time)

%let end = catt(',&Date_End,time)

 

data work.Time_Sourced;
set work.Time_Sourced;
where WEBU_Datetime >=&begin and WEBU_Datetime <= &end;
run;

 

But that isn't working unsurprisingly. I believe the catt function is not operating the way I want it to. Any suggestions? 

 

 


Accepted Solutions
Solution
‎07-05-2016 07:55 PM
Super User
Super User
Posts: 6,383

Re: Using macro variable to refer to Date.

Macro triggers do not work inside of single quotes. Use double quotes instead.

%let begin=27JUN2016 ;
%let end= 04JUL2016 ;

data Time_Sourced;
  set Time_Sourced;
  where "&begin:06:00"dt <= WEBU_Datetime <= "&end:05:59"dt;
run;

View solution in original post


All Replies
Solution
‎07-05-2016 07:55 PM
Super User
Super User
Posts: 6,383

Re: Using macro variable to refer to Date.

Macro triggers do not work inside of single quotes. Use double quotes instead.

%let begin=27JUN2016 ;
%let end= 04JUL2016 ;

data Time_Sourced;
  set Time_Sourced;
  where "&begin:06:00"dt <= WEBU_Datetime <= "&end:05:59"dt;
run;
Contributor
Posts: 32

Re: Using macro variable to refer to Date.

Wow. 

 

That was much more easy than I thought it would be. 

 

Thank you very much! 

 

 

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 239 views
  • 0 likes
  • 2 in conversation