topic SQL query in SAS Programming

SQL query

UshaLatha — Mon, 12 Nov 2018 09:16:44 GMT

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

Re: SQL query

RW9 — Mon, 12 Nov 2018 09:25:05 GMT

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.

Re: SQL query

UshaLatha — Mon, 12 Nov 2018 09:36:16 GMT

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

Re: SQL query

andreas_lds — Mon, 12 Nov 2018 09:42:39 GMT

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;

Re: SQL query

RW9 — Mon, 12 Nov 2018 10:05:13 GMT

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.

Re: SQL query

RW9 — Mon, 12 Nov 2018 10:08:48 GMT

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

Re: SQL query

UshaLatha — Mon, 12 Nov 2018 10:39:41 GMT

Its DB2.

Re: SQL query

UshaLatha — Mon, 12 Nov 2018 10:47:04 GMT

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.

Re: SQL query

RW9 — Mon, 12 Nov 2018 10:54:10 GMT

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.