Using macro variable to refer to Date.

SmcGarrett — Tue, 05 Jul 2016 23:00:34 GMT

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?

Re: Using macro variable to refer to Date.

Tom — Tue, 05 Jul 2016 23:44:57 GMT

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;

Re: Using macro variable to refer to Date.

SmcGarrett — Tue, 05 Jul 2016 23:56:05 GMT

Wow.

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

Thank you very much!

topic Using macro variable to refer to Date. in SAS Enterprise Guide

Using macro variable to refer to Date.

Re: Using macro variable to refer to Date.

Re: Using macro variable to refer to Date.