Hi,
I'm trying to build a macro that would automate a date range of data for my query.
This macro would generate data for an entire week.
I've tried the following but nothing works when I invoke my variable name in my query
Proc SQL;
%let DATASET_DATE BETWEEN '18aug2017'd AND '20aug2017'd;
quit;
any help would be great!!!
@sabjacs wrote:
Hi,
I'm trying to build a macro that would automate a date range of data for my query.
This macro would generate data for an entire week.
I've tried the following but nothing works when I invoke my variable name in my query
Proc SQL;
%let DATASET_DATE BETWEEN '18aug2017'd AND '20aug2017'd;
quit;
Syntax for a Let statement is:
%let macrovariablename = some sort of string values;
You are missing the =. Since you do not show HOW you attempted to use the macro variable we have NO WAY to guess exactly which of a plethora of potential problems you participated in.
If you cannot share your data and can't make a dummy set then use a SAS supplied data set. The code below creates a set with dates so I'll show a brief example using it.
Here is a short example of one way to pass a couple of dates into macro:
%macro dummy (startdate=, enddate=); /* pass macro variables as dates in date9. format*/ data work.dummy; do date= "&startdate"d to "&enddate"d; output; end; format date mmddyy10.; run; %mend; %dummy(startdate= 15Feb2017, enddate= 4Mar2017);
Note that I pass a value without quotes and place the macro variable into a date literal with double quotes. Passing quotes as parameters is a common cause of confusing program.
%macro dateselect( dsname=, startdate=, enddate=); proc sql; create table work.dateselect as select * from &dsname where Date between "&Startdate"d and "&Enddate"d ; quit; %mend; %dateselect (dsname=work.dummy,startdate= 25Feb2017, enddate= 1Mar2017);
Note that my Proc sql includes what it is doing (creating a table in this case) , selecting variables, designating a source data set
and then applying the where clause.
Hi Reeza,
yes I have a working code, its quite large and I dont want to go through it each time to change the dates, thats why I want to built a macro which I can easily update without going through all my codes.
Then post skeleton code that you can test. Unfortunately your code is not close to correct.
Hi,
I'm trying to build a macro that would automate a date range of data for my query.
This macro would generate data for an entire week.
I've tried the following but nothing works when I invoke my variable name in my query
Proc SQL;
%let DATASET_DATE BETWEEN '18aug2017'd AND '20aug2017'd;
quit;
any help would be great!!!
This is a very simple example, and really no macros or macro variables are needed here. It could be that the real-life problem is more complex, and perhaps macros are needed, although even there I am somewhat skeptical that this needs to be done via a macro.
Here is the fix to your code.
%let macrovariablename = DATASET_DATE BETWEEN '18aug2017'd AND '20aug2017'd;
Note that this does not belong inside PROC SQL.
Show what the Proc SQL code without any macros would be.
Then tell us which parts you are attempting to replace with macro values.
Your example has several issues. One there is not data source for proc sql to operate on, there is no instruction as to what operation, such as Select or update or insert.
Your Let statement is incomplete. There should be an = in there after the name of the macro variable.
And placing %let statements in the middle of Proc or datastep code is poor practice. The macro values should be defined before the step that attempts to use them.
@sabjacs wrote:
Hi,
I'm trying to build a macro that would automate a date range of data for my query.
This macro would generate data for an entire week.
I've tried the following but nothing works when I invoke my variable name in my query
Proc SQL;
%let DATASET_DATE BETWEEN '18aug2017'd AND '20aug2017'd;
quit;
Syntax for a Let statement is:
%let macrovariablename = some sort of string values;
You are missing the =. Since you do not show HOW you attempted to use the macro variable we have NO WAY to guess exactly which of a plethora of potential problems you participated in.
If you cannot share your data and can't make a dummy set then use a SAS supplied data set. The code below creates a set with dates so I'll show a brief example using it.
Here is a short example of one way to pass a couple of dates into macro:
%macro dummy (startdate=, enddate=); /* pass macro variables as dates in date9. format*/ data work.dummy; do date= "&startdate"d to "&enddate"d; output; end; format date mmddyy10.; run; %mend; %dummy(startdate= 15Feb2017, enddate= 4Mar2017);
Note that I pass a value without quotes and place the macro variable into a date literal with double quotes. Passing quotes as parameters is a common cause of confusing program.
%macro dateselect( dsname=, startdate=, enddate=); proc sql; create table work.dateselect as select * from &dsname where Date between "&Startdate"d and "&Enddate"d ; quit; %mend; %dateselect (dsname=work.dummy,startdate= 25Feb2017, enddate= 1Mar2017);
Note that my Proc sql includes what it is doing (creating a table in this case) , selecting variables, designating a source data set
and then applying the where clause.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.