macro to extract monthly report

[bondtk]

Hi

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;

[Kurt_Bremser]

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)

[bondtk]

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

[Kurt_Bremser · Accepted solution]

---

@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');

[Astounding]

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)?

[bondtk]

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

[Astounding]

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.

[bondtk]

Hi

I tried this macro, it came up with error

Error: where clause operator requires compatible variables.

Please advise.

[Astounding]

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.

[bondtk]

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.

[bondtk]

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.

[Kurt_Bremser]

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.

[Astounding]

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 ;

[bondtk]

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

[Kurt_Bremser]

---

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