SAS Macro code:

[bondtk]

Hi

I am quite new to SAS macros.

I need help if someone could explain me this code in layman's term , step by step code explanation so that I could develop

some understanding how it works.  This macro is to define start date and end date and using to extract data and also

to create datestamp for files names as well.  Please advise in simple way as I am not very experienced in this.

thanks

TK

Code is below:

/*startmonth - 'DDMMYYY'd*/

/*datestamp = YYYYMM*/

%let startdt = 01NOV2016;

%let finishdt = 01AUG2017;

%let datestamp = %sysfunc(intnx(month,"&finishdt"d,0,b),year4.)%sysfunc(putn(%sysfunc(intnx(month,"&finishdt"d,0,b),month3.),z2.));

　

%macro extract(start,finish);

%let enddate = %sysfunc(intnx(month,"&finish."d,0,b),date9.);

%let datestamp = %sysfunc(intnx(month,"&finish"d,0,b),year4.)%sysfunc(putn(%sysfunc(intnx(month,"&finish"d,0,b),month3.),z2.));

data _null_;

call symputx('startmonth',compress("'"||"&start."||"'d"));

call symputx('endmonth',compress("'"||"&finish."||"'d"));

run;

%put &startmonth.;

%put &endmonth.;

%put &enddate.;

%put &datestamp.;

data tk.application_extract_&datestamp.;

set sastrain.application_extract;

where &startmonth <= datepart(d_entry) <= &endmonth;

run;

%mend extract;

%extract(&startdt.,&finishdt.);

[Kurt_Bremser]

Instead of those eye-soring sysfunc avalanches, do your date calculations in the data step, where they belong. It's WAY easier to debug them there.

A YYYYMM month stamp can be created in one step with the yymmn6. format.

[bondtk]

Hi

I asked this question if someone can elaborate in layman term to make me understand
This code first.

And if there is an easy way to write this code to achieve the same result then it
It will be very handy to know.

Thanks
Tk

[Patrick]

@bondtk

I believe you understand already what this macro does: You pass in two values and then based on these values the macro creates a bunch of macro variables and then uses these macro variables in a data step.

Anything further: That's code and if you want to understand it then there is documentation where you can lookup all of the statements and functions used. On this low level there isn't any layman's language anymore. It's code and you need to skill-up and become the experts if you want to understand.

[bondtk]

Hi Patrick
If I know how this code works, the I would'nt have wasted everyone time to answer
The question, don't worry I will keep on trying and try to understand the documentation
As well, hopefully I will make something it of it in a months time...

Thanks

Tk

[Kurt_Bremser]

In addition to the fine explanation @Tom gave you, there is no need to store dates (or datetimes) in macro variables in human-readable form, if you want to use them as values in code:

data _null_;
call symputx('startdt',put(input('01NOV2016',date9.),best.));
call symputx('finishdt',put(input('01AUG2017',date9.),best.));
run;

Then simplify your macro:

%macro extract(start,finish);
data tk.application_extract_%sysfunc(putn(&finish,yymmn6.));
  set sastrain.application_extract;
  where &start  <= datepart(d_entry) <= &finish;
run;
%mend extract;

and call it accordingly:

%extract(&startdt,&finishdt)

Edit: changed member reference to @Tom

[bondtk]

Hi Kurt

So please advise what's wrong with this code: This code below worked fine , so why I have to use routines here  , what

advantage can symput gives me over this code below and frankly speaking I don't understand these routines anyway...

 %let startdt = 01NOV2016;

%let finishdt = 01FEB2017;

%let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));

%macro extract(start,finish);

%let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));

%let startmonth = "&start."d ;

%let endmonth = "&finish."d ;

data tk.application_extract_&datestamp.;

set sastrain.application_extract;

where "&start"d <= datepart(d_entry) <= "&finish"d;

run;

%mend extract;

%extract(&startdt.,&finishdt.);

[Kurt_Bremser]

I simply prefer to use data _null_ steps and call symput/symputx because it removes the need to think about all the quoting issues.

Similarly, I prefer to have values in macro variables that are as simple as possible. Hence my post about using raw date/datetime values instead of formatted ones.

Remember, someone has to maintain the code in the future, and that someone may well be you, and you'll curse your former self for any unnecessary piece of code that makes your work harder.

[Tom · Accepted solution]

Let's break it down. First this block:

%let startdt = 01NOV2016;
%let finishdt = 01AUG2017;
%let datestamp = %sysfunc(intnx(month,"&finishdt"d,0,b),year4.)%sysfunc(putn(%sysfunc(intnx(month,"&finishdt"d,0,b),month3.),z2.));

It is setting two macro variables dates represented in DATE9 format.

Then it does a really convoluted process to get the YEAR and MONTH part of the second one and put them back together in YYYYMON order.  The %sysfunc() macro function lets you use data step functions in macro code. The optional second argument let's you control what format SAS uses to generate the string that is returned.  The INTNX() function let's you calculate a change in date.  The "..."d is how to represent a date literal. The value inside the quotes must be something the DATE informat will recognize.

So the first %sysfunc() call converts FINISHDT to a four digit year.  You could have just done %substr(&finishdt,6), but that would only work if FINISHDT is formatted using exactly 9 characters. The function call would work with other equivalent representations like 1AUG2017, 1AUG17,01-AUG-2017, etc.  The second one is getting the two digit month number. 

Note that the INTNX() function calls are not really doing anything since you are just moving by intervals of zero length.

Why not just replace with:a single function call?

%let datestamp=%sysfunc(putn("&finishdt"d,yymmn6.));

So what does the macro do?  The %MACRO and %MEND statements mark the beginning and ending of the macro definition. The macro is defined to take two parameters and can both be passed by position if you want.   When the macro starts the values of the local macro variables START and FINISH will be set to the values that were passed when the macro was called.

So these two statements.

%let enddate = %sysfunc(intnx(month,"&finish."d,0,b),date9.);
%let datestamp = %sysfunc(intnx(month,"&finish"d,0,b),year4.)%sysfunc(putn(%sysfunc(intnx(month,"&finish"d,0,b),month3.),z2.));

The second is like the code above and could be simplified in a similar way. The first just converts any valid date literal value in the input parameter FINISH into DATE9 format and store it in the macro variable ENDATE.  Looks like a little format standardization?

Next 

data _null_;
  call symputx('startmonth',compress("'"||"&start."||"'d"));
  call symputx('endmonth',compress("'"||"&finish."||"'d"));
run;

Looks like an attempt to create date literals from the input parameters.  Perhaps the person that wrote this didn't understand that SAS doesn't care whether you use single quotes or double quotes in date literals?  This could just as easily be done like this:

%let startmonth = "&start."d ;
%let endmonth = "&finish."d ;

Next are just code to dump values to the log.

Then finally it looks like the macro gets to doing some actual work.

data tk.application_extract_&datestamp.;
  set sastrain.application_extract;
  where &startmonth <= datepart(d_entry) <= &endmonth;
run;

So here they are making a dataset where the output name includes the 6 digit YYYYMM formatted date value and the values selected fall within the range of date defined by the input parameters.

So the whole macro can simply be reduced to this:

%macro extract(start,finish);
data tk.application_extract_%sysfunc(putn("&finish"d,yymmn6)) ;
  set sastrain.application_extract;
  where "&start"d  <= datepart(d_entry) <= "&finish"d;
run;
%mend extract;

[bondtk]

Hi Tom

Thanks a lot for taking time and explain this to me in a very easy format.

So I have changed the code as per your instructions and it worked..

Code is :

%let startdt = 01NOV2016;

%let finishdt = 01FEB2017;

%let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));

%macro extract(start,finish);

%let enddate = %sysfunc(intnx(month,"&finish."d,0,b),date9.);

%let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));

%let startmonth = "&start."d ;

%let endmonth = "&finish."d ;

data tk.application_extract_&datestamp.;

set sastrain.application_extract;

where "&start"d <= datepart(d_entry) <= "&finish"d;

run;

%mend extract;

%extract(&startdt.,&finishdt.);

I just need to ask few questions for my understanding and learning.

In this code,  three variables are defined outside the macro, do we call them global variables as they are defined outside the macro,  I think the reason datestamp

Variable is defined outside the macro so we can use this datestamp to other codes in the same project whenever we create other files with same datestamp. So that means

Startdt and finishdate could be defined inside the macro or do they have to define outside the macro,  that’s first question.

2nd question:

04 variables are defined inside the macro,  

• enddate variable is not used anywhere apart from the %put  function to see on the log so that can be removed , isn’t it ?
• Startmonth and endmonth  variables in the macro , we use “&start.”d ,  so the reason was to define the date literal , and we need to  dot after the start and it has to be in double quotes.
• Datestamp , I used the same way as it was outside the macro.  We use putn(“&finishdt”d, yymmn6.))..    we use putn  with finishdt to change it to numeric format?  Is it true?  %sysfunction is only used Within macro to able to use putn.

3rd question:

When we define the variable we use   %let startmonth = "&start."d ;

However when we use the where statement , we write as “&start”d  , no dot after the start, please advise on that.

where "&start"d <= datepart(d_entry) <= "&finish"d;

4th question:

If I don’t need start date and only need the finish date so I can extract all data from the finish date

, can I write like this: and remove the startmonth variable.

%let finishdt = 01FEB2018;

%let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));

%macro extract(finish);

%let enddate = %sysfunc(intnx(month,"&finish."d,0,b),date9.);

%let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));

%let endmonth = "&finish."d ;

data tk.application_extract_&datestamp.;

set sastrain.application_extract;

where datepart(d_entry) <= "&finish"d;

run;

%mend extract;

%extract(&finishdt.);

question 5:

As I have removed the 

Data _null_ 

And symputx  routines from this code as it was not required.

I am just curious when do we require symput routines and why do we use it?

And if you suggest any good book or any other source to understand macros , that will be really handy.

Please advise.

Thanks for your time.

TK

[Kurt_Bremser]

See comments:

%let startdt = 01NOV2016;
%let finishdt = 01FEB2017;

%let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));
/* this statement is unnecessary, as it only creates a variable in the global
symbol table that is never used, as it will be overridden by the local one */

%macro extract(start,finish);

%let enddate = %sysfunc(intnx(month,"&finish."d,0,b),date9.);
/* &enddate is never used, so remove this statement */

%let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));
/* the %sysfunc can be moved into the data statement */

%let startmonth = "&start."d ;
%let endmonth = "&finish."d ;
/* both of these are never used, so remove them also */

data tk.application_extract_&datestamp.;
set sastrain.application_extract;
where "&start"d <= datepart(d_entry) <= "&finish"d;
run;

%mend extract;

%extract(&startdt.,&finishdt.);

Always strive to keep your code as simple as possible, see Maxim 37.

[bondtk]

Hi Kurt

I appreciate your comments:

the reason I used the  %let datestamp outside the macro as this variable will be used later on when I create other reports based on the same data extract.

when you say to remove these 02,  I think we are using them with the where statement , don't we, please clarify.

%let startmonth = "&start."d ;

%let endmonth = "&finish."d ;

 I have removed the %enddate as well.

code:

%let startdt = 01NOV2016;

%let finishdt = 01FEB2017;

%let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));

%macro extract(start,finish);

%let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));

%let startmonth = "&start."d ;

%let endmonth = "&finish."d ;

data tk.application_extract_&datestamp.;

set sastrain.application_extract;

where "&start"d <= datepart(d_entry) <= "&finish"d;

run;

%mend extract;

%extract(&startdt.,&finishdt.);

[Kurt_Bremser]

Is there any use of &startmonth or &endmonth? Answer: no. Therefore remove these unnecessary statements, they only create local macro variables that are never used.

[Kurt_Bremser]

---

@bondtk wrote:

Hi Kurt

I appreciate your comments:

the reason I used the  %let datestamp outside the macro as this variable will be used later on when I create other reports based on the same data extract.

---

This is a valid reason. But it gives you even more reason to incorporate the %sysfunc into the data statement

data tk.application_extract_%sysfunc(putn(&finish,yymmn6.));

as it removes the ambiguous use of the name datestamp that will cause confusion sometime down the line.

[bondtk]

Hi Kurt

This is a valid reason. But it gives you even more reason to incorporate the %sysfunc into the data statement

data tk.application_extract_%sysfunc(putn(&finish,yymmn6.));

as it removes the ambiguous use of the name datestamp that will cause confusion sometime down the line.

1.  isn't it better to write :

data tk.application_extract_&datestamp.;    

rather than replace the &datestamp with  %sysfunc(putn($finish, yymmn6.))

as datestamp will bring the same thing and easy to apply as its already defined as variable and its a simple code

so I don't see any confusion about it.  Please clarify your confusion about it.

2.  You mentioned to remove

%let startmonth = "&start."d;

%let endmonth =  "&finish."d;

so I am confused as  I thought we are using them in the data statement :  Please clarify if that's not the case.

data tk.application_extract_&datestamp.;

set sastrain.application_extract;

where "&start"d <= datepart(d_entry) <= "&finish"d;

run;

**************************************************

If you have to write this code with global variable as datestamp which you can use anywhere in other programs.

How would you write it from start to finish.

%let startdt = 01NOV2016;

%let finishdt = 01FEB2017;

%let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));

　

%macro extract(start,finish);

%let datestamp = %sysfunc(putn("&finishdt"d, yymmn6.));

%let startmonth = "&start."d ;

%let endmonth = "&finish."d ;

%put &startmonth.;

%put &endmonth.;

%put &datestamp.;

data tk.application_extract_&datestamp.;

set sastrain.application_extract;

where "&start"d <= datepart(d_entry) <= "&finish"d;

run;

%mend extract;

%extract(&startdt.,&finishdt.);