First and last day of month using Macros in proc sql

[forumsguy]

Hi all,

My requirement is to use a filter code to select only current month's data from given table and condition I need to select is where report _date >= first_day_of_month and report_date <= last_day_of_month

My data looks like this

Account_id        Report_date

1                           12JUN2013

2                            17JAN2014

3                            19MAY2014

The code that I need to modify (I wrote this )

%let month_key=201405;

Proc Sql;

Create table work.want as

Select * from Work.have

Where report _date >= intnx ('month', &month_key,0) and report_date<=intnx('month',&month_key,0)-1;

Quit;

The value of month_key will be passed like this only. Don't know where I am going wrong but it's producing wrong output.

Need your help on this. 

Thanks in advance.

[Linlin]

data have;
input Account_id        Report_date : date9.;
format report_date date9.;
cards;
1                           12JUN2013
2                            17JAN2014
3                            19MAY2014
; 

%let month_key=201405;

Proc Sql;
Create table work.want as
Select * from Work.have
Where year(report_date)=%substr(&month_key,1,4) and month(report_date)=%substr(&month_key,5);
Quit;

[Quentin]

Hi,

INTNX wants a SAS Date, and 201405 is not a SAS date (at least it's not the SAS date you want).

You can use INPUT() to convert 201405 to a SAS date, something like: (untested)

%put %sysfunc(inputn(201405,yymmn6.),mmddyy10.); 
%put %sysfunc(intnx (month,%sysfunc(inputn(201405,yymmn6.)) ,0,B),mmddyy10.);
%put %sysfunc(intnx (month,%sysfunc(inputn(201405,yymmn6.)) ,0,E),mmddyy10.);

Where
  report _date     ge %sysfunc(intnx (month,%sysfunc(inputn(201405,yymmn6.)) ,0,B))
  and report_date le %sysfunc(intnx (month,%sysfunc(inputn(201405,yymmn6.)) ,0,E))
;

Actually you probably dont need the %sysfunc, and could instead just replace &month_key in your approach with inputn(&monthkey,yymmn6.)  .  But sometimes the macro language cam makes it easier to test/debug (not sure I've ever said *that* before...

HTH,

-Q.

[Linlin · Accepted solution]

tested:

Proc Sql;

Create table work.want as

Select * from Work.have

Where report_date between intnx('month',%sysfunc(inputn(&month_key,yymmn6.)),0,'b') and intnx('month',%sysfunc(inputn(&month_key,yymmn6.)),0,'e');

Quit;

or

Proc Sql;

Create table work.want2 as

Select * from Work.have

Where report_date between intnx('month',input("&month_key",yymmn6.),0,'b') and intnx('month',input("&month_key",yymmn6.),0,'e');

Quit;