BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sabjacs
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

View solution in original post

8 REPLIES 8
Reeza
Super User
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/
sabjacs
Calcite | Level 5

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.

 

Reeza
Super User

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

sabjacs
Calcite | Level 5

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

PaigeMiller
Diamond | Level 26

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
ballardw
Super User

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. 

ballardw
Super User

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

sabjacs
Calcite | Level 5
thanks a lot @ ballardw the solution works

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 4810 views
  • 2 likes
  • 4 in conversation