topic Re: Creating macro for data in a specific date range in SAS Enterprise Guide

Creating macro to get data from a specific date range

sabjacs — Tue, 22 Aug 2017 15:32:24 GMT

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

Creating macro for data in a specific date range

sabjacs — Tue, 22 Aug 2017 15:33:34 GMT

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

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

Reeza — Tue, 22 Aug 2017 15:34:54 GMT

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/

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

sabjacs — Tue, 22 Aug 2017 15:37:16 GMT

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.

Re: Creating macro for data in a specific date range

PaigeMiller — Tue, 22 Aug 2017 15:57:13 GMT

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.

Re: Creating macro for data in a specific date range

ballardw — Tue, 22 Aug 2017 15:43:04 GMT

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.

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

Reeza — Tue, 22 Aug 2017 15:48:15 GMT

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

Re: Creating macro for data in a specific date range

ballardw — Tue, 22 Aug 2017 17:53:38 GMT

@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.

Re: Creating macro for data in a specific date range

sabjacs — Tue, 22 Aug 2017 18:09:17 GMT

thanks a lot @ ballardw the solution works