topic Re: macro to extract monthly report in SAS Enterprise Guide

macro to extract monthly report

bondtk — Wed, 07 Mar 2018 08:51:40 GMT

I have got this code to extract my data, wondering if there is any macro possible so that it extracts monthly data and I don't have to

change dates all the time. Any macro possible so I can only select a month or just change month name in macro extract monthly data,

please advise.

data application_extract;

set retail .xy_BBS_masterflow;

where d_entry >= '2016-11-01 00:00:00.000' and d_entry < '2017-10-01 00:00:00.000' ;

run;

Re: macro to extract monthly report

Kurt_Bremser — Wed, 07 Mar 2018 08:58:57 GMT

Just an example:

data have;
input d_entry e8601dt24.3;
format d_entry e8601dt24.3;
cards;
2016-11-01 00:00:00.000
2016-11-02 00:00:00.000
2016-12-02 00:00:00.000
;
run;

%macro select(year,month);
data want;
set have;
where month(datepart(d_entry)) = &month and year(datepart(d_entry)) = &year;
run;
%mend;

%select(2016,11)

Re: macro to extract monthly report

Astounding — Wed, 07 Mar 2018 13:30:41 GMT

Clearly, you will have to supply a year as well as a month.

Do you always want an 11-month time period?

Does the code you posted work (meaning that d_entry is a character string)?

Re: macro to extract monthly report

bondtk — Wed, 07 Mar 2018 22:52:55 GMT

Hi

11 month is not mandatory, it could be 3 months , 06 months or monthly extract so I am looking what will be easiest way to capture that in a macro...

Please advise

Re: macro to extract monthly report

Astounding — Thu, 08 Mar 2018 05:51:21 GMT

Since the time period can vary, the user will need to pass both the starting point and the ending point. This might be the macro call:

%extract (start=2016-11, finish=2017-10)

Users will need to be educated about the FINISH month itself being excluded from the extracted data. The macro definition might look like this:

%macro extract (start=, finish=);

data application_extract;

set retail .xy_BBS_masterflow;

where ("&start-01 00:00:00.000" <= d_entry < "&finish-01 00:00:00.000") ;

run;

%mend extract;

There are other styles that can be used, if the start and finish points would need to be utilized at other points within the same program.

Notice that double-quotes are being used. Single quotes cause an error, preventing macro variables from resolving into their assigned values.

All in all, using macro language here doesn't save a ton of work. You could even argue that the original program is just as good as the one that uses macro language.

Re: macro to extract monthly report

bondtk — Fri, 09 Mar 2018 10:28:34 GMT

Hi

I tried this macro, it came up with error

Error: where clause operator requires compatible variables.

Please advise.

Re: macro to extract monthly report

bondtk — Fri, 09 Mar 2018 11:05:34 GMT

Hi

Thanks for your reply, please advise if I want to run it for 3 months or 6 months extract , how do I change it, please advise , I am not good with macros...

Thanks
Tk

Re: macro to extract monthly report

Kurt_Bremser — Mon, 12 Mar 2018 06:46:49 GMT

@bondtk wrote:
Hi

Thanks for your reply, please advise if I want to run it for 3 months or 6 months extract , how do I change it, please advise , I am not good with macros...

Thanks
Tk

Supply a startmonth and endmonth as macro parameters, and then compare datepart() of your timestamps with both

where mdy(&startmonth,1,&startyear) <= datepart(d_entry) <= intnx('month',mdy(&endmonth,1,&endyear),0,'e');

Re: macro to extract monthly report

Astounding — Sat, 10 Mar 2018 22:51:13 GMT

This is telling you that the original WHERE clause you posted was incorrect. Most likely, you omitted the characters dt:

where ("&start-01 00:00:00.000"dt <= d_entry < "&finish-01 00:00:00.000"dt) ;

But I'm guessing here. You'll have to take a look at your original program, take a properly working version, and show what it looks like. Until then, macro language cannot help.

Re: macro to extract monthly report

bondtk — Sun, 11 Mar 2018 03:34:05 GMT

Hi Astounding

I tried this :

%macro extract (start=, finish=);

data tk.application_extract_jun_aug;

set sastrain.application_extract;

where ("&start-01 00:00:00.000"dt <= d_entry < "&finish-01 00:00:00.000"dt) ;

run;

%mend extract;

%extract (start=2017-06, finish=2017-08)

came with error:

ERROR: Invalid date/time/datetime constant "2017-06-01 00:00:00.000"dt.

ERROR: Invalid date/time/datetime constant "2017-08-01 00:00:00.000"dt.

ERROR: Syntax error while parsing WHERE clause.

please advise.

Re: macro to extract monthly report

bondtk — Sun, 11 Mar 2018 03:55:02 GMT

worked without macro

data tk.application_extract_jun_aug;

set sastrain.application_extract;

where d_entry >= "01JUN2017:00:00:00.000"dt and d_entry < "01SEP2017:00:00:00.000"dt ;

run;

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

so what changes I could make in the macro: as macro gives the error:

%macro extract (start=, finish=);

data tk.application_extract_jun_aug;

set sastrain.application_extract;

where ("&start-01 00:00:00.000"dt <= d_entry < "&finish-01 00:00:00.000"dt) ;

run;

%mend extract;

%extract (start=2017-06, finish=2017-08)

Errors:

ERROR: Invalid date/time/datetime constant "2017-06-01 00:00:00.000"dt.

ERROR: Invalid date/time/datetime constant "2017-08-01 00:00:00.000"dt.

ERROR: Syntax error while parsing WHERE clause.

Re: macro to extract monthly report

Kurt_Bremser — Sun, 11 Mar 2018 09:09:35 GMT

In a datetime literal, SAS always expects the date part to be written in the ddmmmyyyy format like 01jan1960.

For most purposes, I prefer to store the raw, unformatted numerical values of dates or datetimes in macro variables, as they can be used in calculations and comparisons without any wrappers.

If you need to use a date for comparison with a datetime, just multiply the raw value by 86400.

Re: macro to extract monthly report

Astounding — Sun, 11 Mar 2018 14:03:41 GMT

I don't get it. If this statement worked without macros, why would you want macros to construct a statement that looks different than this?

where d_entry >= "01JUN2017:00:00:00.000"dt and d_entry < "01SEP2017:00:00:00.000"dt ;

Re: macro to extract monthly report

bondtk — Sun, 11 Mar 2018 18:56:34 GMT

Hi Astounding

I am confused now with your answer, so you are saying that I am trying to do something different with the macro,

All I am trying to remove this long date format to simply write the month and year to run the extract.

If you could suggest an option to achieve that, as I am hopeless with the macros..

Thanks
TK

Re: macro to extract monthly report

Kurt_Bremser — Mon, 12 Mar 2018 06:56:31 GMT

@bondtk wrote:
Hi Astounding

I am confused now with your answer, so you are saying that I am trying to do something different with the macro,

All I am trying to remove this long date format to simply write the month and year to run the extract.

If you could suggest an option to achieve that, as I am hopeless with the macros..

Thanks
TK

In your macro-less code, you supplied correct SAS datetime literals. In your macrotized code, you changed the format of the date in the datetime literals from ddmmmyyyy (as in 01jan1960) to yyyy-mm-dd (as in 1960-01-01), which is not recognized.

Try this:

%macro extract(start=,finish=);

data _null_;
call symputx('startmonth',put(dhms(input("&start.-01",yymmdd10.),0,0,0),best.));
call symputx('endmonth',put(dhms(input("&finish.-01",yymmdd10.),23,59,59),best.));
run;

data tk.application_extract_jun_aug;
set sastrain.application_extract;
where &startmonth <= d_entry <= &endmonth;
run;

%mend extract;

%extract(start=2017-06,finish=2017-08)

Re: macro to extract monthly report

bondtk — Mon, 12 Mar 2018 09:11:34 GMT

Hi Kurt

Thanks for the solution, really appreciate that..

now i I am just trying to understand the code , is symputx function is used to convert the date in to character value , could you please

elaborate the whole code step by step as I am not familiar with symputx, put dims and the use input, if you could help me understand this code then it will be very beneficial for my learning. Please advise.

thanks

Re: macro to extract monthly report

Kurt_Bremser — Mon, 12 Mar 2018 09:37:53 GMT

First of all, symputx is not a function, it is a call routine (in Pascal such a thing is called a procedure, the generic name in programming is subroutine; C does not have it, it is defined there as a function without a return value, eg void myproc();)

It does not return a value and always needs the call keyword.

Having said that, let's dissect one of the statements from outside in:

call symputx('startmonth',something_complicated_here);

The first parameter (or argument) to call symputx is the name of a macro variable to be created. The second parameter is the value that should go into this new macro variable. An optional third parameter would specifiy if the variable is to be created in the local ('l') or global ('g') symbol table.

Next layer:

put(some_number,best.)

Since call symputx (and call symput, BTW) want a character value as their second argument, I use the put() function to prevent any automatic conversion (I like to have complete control over what the computer does for me). The additional leading blanks from the best. format are being taken care of by call symputx.

Next layer:

dhms(input("&start.-01",yymmdd10.),0,0,0)

The dhms() function computes a datetime value from 4 parameters: date, hours, minutes, seconds. The date needs to be a valid SAS date value (number of days calculated from 1960-01-01, back in time to 1582, and forward to 19999, but limited by the four digits for years in most date formats)

Innermost layer:

input("&start.-01",yymmdd10.)

Make a yyyy-mm-dd string from your macro variable, and use the proper informat to convert the string to a SAS date value.

Re: macro to extract monthly report

bondtk — Mon, 12 Mar 2018 09:52:34 GMT

Fantastic, I think I learn a new thing today, really appreciate your time,
Just one question , when you added 0,0,0 after the format , are these for h, m and seconds.

Thanks

Tk

Re: macro to extract monthly report

Kurt_Bremser — Mon, 12 Mar 2018 09:56:36 GMT

Quote:

The dhms() function computes a datetime value from 4 parameters: date, hours, minutes, seconds

Re: macro to extract monthly report

Astounding — Mon, 12 Mar 2018 13:24:26 GMT

It may not be relevant, since you already have a solution. But here is the point I was trying to make. Perhaps this will help you frame questions in the future.

Here's what you started asking for:

where d_entry >= '2016-11-01 00:00:00.000' and d_entry < '2017-10-01 00:00:00.000' ;

Here's what you actually needed (using a different date range, but that's not the point here):

where d_entry >= "01JUN2017:00:00:00.000"dt and d_entry < "01SEP2017:00:00:00.000"dt ;

You need to realize that these statements are different, and the solution will vary depending on what you actually need.