SQL query

[UshaLatha]

Hi,

I have following query where we are pulling the data of current and next month for a particular year.

proc sql;
select id as
from table1 A inner join table2 B on A.id=B.id
where year(date)>=2017
and (month(date)=month(today())+1 or month(date)=month(today()));
quit;

But this will not work for December (12+1 becomes 13). Please suggest how can the code work for December onward.

Thanks

[RW9]

I assume that this is not pass through to a database, in which case intnx should be used for this:

proc sql;
  select id
  from   table1 a 
  inner join table2 b 
  on     a.id=b.id
  where intnx("month",today(),0,"b") <= date <= intnx("month",today(),1,"e");
quit;

So I take lower bound as start of this month, and end bound as end of next month.

[UshaLatha]

This is a pass-through to database.. intnx is not working

[andreas_lds]

Then move intnx to a macro-variable

%let startDate = %sysfunc(intnx("month",today(),0,"b"));
%let untilDate = %sysfunc(intnx("month",today(),1,"e"));

proc sql;
  select id
  from   table1 a 
  inner join table2 b 
  on     a.id=b.id
  where &startDate. <= date <= &untilDate.;
quit;

[RW9]

Not sure if that would work directly.  Would not the macro variables store the number of day since cuttoff?  If so the database would not know what that number is.  What you might need to is to pass in date strings, maybe something like:

data _null_;
  call symputx("startdate",put(intnx("month",today(),0,"b"),date9.);
  call symputx("untildate",put(intnx("month",today(),1,"b"),date9.);
run;
proc sql;
...
  where to_date("&startdate.") <= date <= to_date("&untildate.")
...

Just a thought (oracle syntax above by the way).

[UshaLatha]

can you explain how does this work: 

where to_date("&startdate.") <= date <= to_date("&untildate.")

as to_date() is not a sas or sql function that I know of. Please correct me if I am wrong. 

[RW9]

Please see the note below that code, I was taking Oracle DB syntax as I did not know your database at the time.  Quick search shows:

https://stackoverflow.com/questions/19500912/to-pull-records-between-two-dates-in-db2

Which would seem to indicate you do not need functions, and simply putting the date to text will work.

[RW9]

This is the kind of information that would be useful upfront.  If this is pass through then you need to use syntax which the database is happy to process.  So you can passthrough a date string, as shown by @andreas_lds using a macro variable.  Essentially this works out the date range before the SQL, and then passes that in as well.  Alternatively there will be functions on the database (I assume) which will handle dates.  Without even knowing which database however I can't help further.

[UshaLatha]

Its DB2.