BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
bondtk
Quartz | Level 8

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;

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

 

View solution in original post

19 REPLIES 19
Kurt_Bremser
Super User

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
Quartz | Level 8
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
Super User

@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
PROC Star

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
Quartz | Level 8
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
PROC Star

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
Quartz | Level 8
Hi

I tried this macro, it came up with error

Error: where clause operator requires compatible variables.

Please advise.
Astounding
PROC Star

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
Quartz | Level 8

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
Quartz | Level 8

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
Super User

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
PROC Star

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
Quartz | Level 8
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
Super User

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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