topic Re: SAS Macro code: in SAS Programming

SAS Macro code:

bondtk — Sun, 18 Mar 2018 06:14:19 GMT

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

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.);

Re: SAS Macro code:

Kurt_Bremser — Sun, 18 Mar 2018 06:40:12 GMT

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.

Re: SAS Macro code:

bondtk — Sun, 18 Mar 2018 07:54:37 GMT

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

Re: SAS Macro code:

Patrick — Sun, 18 Mar 2018 08:29:56 GMT

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

Re: SAS Macro code:

bondtk — Sun, 18 Mar 2018 08:48:37 GMT

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

Re: SAS Macro code:

Tom — Sun, 18 Mar 2018 14:55:34 GMT

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?

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;

Re: SAS Macro code:

Kurt_Bremser — Mon, 19 Mar 2018 07:05:48 GMT

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

Re: SAS Macro code:

bondtk — Mon, 19 Mar 2018 07:18:06 GMT

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.

Re: SAS Macro code:

Kurt_Bremser — Mon, 19 Mar 2018 07:26:04 GMT

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.

Re: SAS Macro code:

bondtk — Mon, 19 Mar 2018 07:27:57 GMT

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.);

Re: SAS Macro code:

Kurt_Bremser — Mon, 19 Mar 2018 07:34:35 GMT

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.

Re: SAS Macro code:

bondtk — Mon, 19 Mar 2018 07:51:20 GMT

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.);

Re: SAS Macro code:

Kurt_Bremser — Mon, 19 Mar 2018 08:07:00 GMT

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.

Re: SAS Macro code:

Kurt_Bremser — Mon, 19 Mar 2018 08:15:01 GMT

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

Re: SAS Macro code:

bondtk — Mon, 19 Mar 2018 20:33:57 GMT

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.);

Re: SAS Macro code:

Tom — Mon, 19 Mar 2018 21:00:53 GMT

If you have already created a macro variable named DATESTAMP with the value you want then there is no need to create it again inside the macro. You could just reference the existing macro variable. Or to make the macro more independent pass the value you already created into the macro as the value of another parameter. Or even better pass into the macro the name of the dataset you want to build and generate the name to pass in the call to the macro using the macro variable you already created in the larger program.

%extract(start=&startdt.,finish=&finishdt.,out=tk.application_extract_&datestamp.);

The macro code you posted is subsetting the data using the input parameters START and FINISH. So there is no need to create new macro variables named STARTMONTH and ENDMONTH. The only thing that the macro does with STARTMONTH and ENDMONTH is write them to the SAS log.

Re: SAS Macro code:

Kurt_Bremser — Tue, 20 Mar 2018 06:31:05 GMT

@Tom: I would never, ever use a macro variable declared outside the macro when the value should be derived from one of the macro parameters.

Unless I had a severe masochistic streak, of course.

if you use the macro in a program that does not have &datestamp, it crashes
if global &datestamp is calculated from other values than those used in calling the macro, hilarious fun ensues.

If one decides to use an extra variable for storing the yyyymm timestamp, then it must be declared local to the macro, to avoid side-effects. Or you introduce it as an additional parameter, as you suggested.

Re: SAS Macro code:

bondtk — Wed, 21 Mar 2018 08:24:59 GMT

Ok Guys

I have simplified the code as much as possible and run it, it ran without any errors..

Please advise if you are happy with this.

I have removed the datestamp variable inside the macro as I want to use datestamp with other programs as well.

%let startdt = 01JAN2016;

%let finishdt = 01FEB2017;

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

%macro extract(start,finish);

data tk.application_extract_&datestamp.;

set sastrain.application_extract;

where "&startdt"d <= datepart(d_entry) <= "&finishdt"d;

run;

%mend extract;

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

I tried both statements with where and both worked , don't know why, If you could clarify.

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

and

where "&startdt"d <= datepart(d_entry) <= "&finishdt"d;

see the difference in "&start"d and "&startdt"d, why these both are acceptable, don't know..

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

I want to ask you Kurt you mention about

data _null_

and the symputx routines to do the same thing. Please advise for my learning

in which circumstances symputx is necessary and whats the advantage of that as compared

to the code that I used, I am sure they do the same thing but whats the advantage of using symputx

and why do we use it and prefer over the other code, just need to understand the difference and usage

of these routines.

Re: SAS Macro code:

Kurt_Bremser — Wed, 21 Mar 2018 09:25:15 GMT

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

%macro extract(start,finish);
data tk.application_extract_&datestamp.;

If datestamp should always be derived from finish, this is sloppy and bad programming, for reasons I have already stated.

This is only valid if finish as supplied as a macro parameter and datestamp as a global macro variable have no fixed relationship.

Regarding

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

vs.

where "&startdt"d <= datepart(d_entry) <= "&finishdt"d;

comes from the fact that in your macro call you used startdt and finishdt as parameters, so the values are the same. If that is what it is planned to be, the whole macro definition is unnecessary, at least the parameters. (you might still create a macro if an identical piece of code is needed several times in your program)

Regarding the use of data steps and symput/symputx:

it allows me to create values with simple data step code, which (at least to me) is much easier (to create and to maintain) than doing it in macro code. The numerous problems that people encounter here on the communities when using macro language tells me that this is a valid point.

Also keep in mind that I am speaking from the POV of ~20 years of SAS experience and the POV of a communities superuser, which should tell you something. After all that time, I still only use macro code when I absolutely have to.

Re: SAS Macro code:

bondtk — Wed, 21 Mar 2018 09:59:16 GMT

Hi Kurt

thanks for your reply , appreciate it,

so if you have to write this code from start to finish , how would you write it , say you are writing it in this macro format , how would you define the date stamp macros as global or local , please advise what would be your flow.

i am asking these silly questions as its my first attempt to write any macro, so be patient.

as far as the data steps and symput is concerned, that's way outside my scope at this stage, may be later on.

thanks