Desktop productivity for business analysts and programmers

macro to extract monthly report

Accepted Solution Solved
Reply
Contributor
Posts: 62
Accepted Solution

macro to extract monthly report

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;

 

 

 


Accepted Solutions
Solution
‎03-12-2018 05:03 AM
Super User
Posts: 10,279

Re: macro to extract monthly report

[ Edited ]

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

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Super User
Posts: 10,279

Re: macro to extract monthly report

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)
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 62

Re: macro to extract monthly report

Posted in reply to KurtBremser
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
Solution
‎03-12-2018 05:03 AM
Super User
Posts: 10,279

Re: macro to extract monthly report

[ Edited ]

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

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 6,785

Re: macro to extract monthly report

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

Contributor
Posts: 62

Re: macro to extract monthly report

Posted in reply to Astounding
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
Highlighted
Super User
Posts: 6,785

Re: macro to extract monthly report

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.

Contributor
Posts: 62

Re: macro to extract monthly report

Posted in reply to Astounding
Hi

I tried this macro, it came up with error

Error: where clause operator requires compatible variables.

Please advise.
Super User
Posts: 6,785

Re: macro to extract monthly report

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.

Contributor
Posts: 62

Re: macro to extract monthly report

Posted in reply to Astounding

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.

Contributor
Posts: 62

Re: macro to extract monthly report

Posted in reply to Astounding

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.

Super User
Posts: 10,279

Re: macro to extract monthly report

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 6,785

Re: macro to extract monthly report

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 ;

Contributor
Posts: 62

Re: macro to extract monthly report

Posted in reply to Astounding
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
Super User
Posts: 10,279

Re: macro to extract monthly report

[ Edited ]

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

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 19 replies
  • 309 views
  • 5 likes
  • 3 in conversation