Desktop productivity for business analysts and programmers

Creating macro to get data from a specific date range

Reply
New Contributor
Posts: 4

Creating macro to get data from a specific date range

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!!!

Super User
Posts: 20,252

Re: Creating macro to get data from a specific date range

To build a macro first develop working SAS code.
What does your working SAS code look like? What are you expecting your macro call to look like?

Are you sure you need a macro?

Here's an intro into macro programming that may be helpful:
https://stats.idre.ucla.edu/sas/seminars/sas-macros-introduction/
New Contributor
Posts: 4

Re: Creating macro to get data from a specific date range

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.

 

Super User
Posts: 20,252

Re: Creating macro to get data from a specific date range

Then post skeleton code that you can test. Unfortunately your code is not close to correct. 

New Contributor
Posts: 4

Creating macro for data in a specific date range

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!!!

Trusted Advisor
Posts: 1,989

Re: Creating macro for data in a specific date range

[ Edited ]

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.

--
Paige Miller
Super User
Posts: 11,578

Re: Creating macro for data in a specific date range

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. 

Super User
Posts: 11,578

Re: Creating macro for data in a specific date range


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.

New Contributor
Posts: 4

Re: Creating macro for data in a specific date range

thanks a lot @ ballardw the solution works

Ask a Question
Discussion stats
  • 8 replies
  • 180 views
  • 2 likes
  • 4 in conversation