Pull data based on current month of date in a second dataset

[TheNovice]

Hi All,

I have 2 queries I need to execute one after another:

First: I need to pull data for the current month in a pass through... the date is in datetime format:

This is what I have tried so far without any results.... I know that I can specify the dates but I am trying to make it so that I don't need to so it can be automated

%Let today = %sysfunc(date(),date9.);
%let sysmonth= %sysfunc(month("&sysdate"d));
%let sysyear= %sysfunc(year("&sysdate"d));

PROC SQL;
connect to oracle (user=&name pass=&pass path=Exa);
create table DF as select *
from connection to Oracle
(select *
from tables
where month(ACTV_DATE) = &sysmonth. )
;
disconnect from Oracle;
quit;

second step.. all these records then have to pull data from a second table where the date ACTV_DATE is after the date in the second table. Again all dates are in datetime format

PROC SQL;
connect to oracle (user=&name pass=&pass path=ExaODIN);
create table DF1 as select *
from connection to Oracle
(select *
from activity
where ID in (select IDfrom df) and datepart(actv_date) >= datepart(secondtable_date)
;
disconnect from Oracle;
quit;

I usually use pass throughs to pull data... i haven't tried segmentation at this level. Really appreciate your help

[Reeza]

You're using SQL Pass through. Your queries need to be Oracle SQL, or PL/SQL not SAS SQL. DATEPART() and MONTH() are not valid functions in Oracle SQL.

extract(month from actv_date) is the correct version of MONTH()

Not sure what datepart() would be, but you could use TRUNC() to change the time part to 0, so that your comparisons would work.

[TheNovice]

Maybe I misunderstood... so how would i make that change the queries?

PROC SQL;
connect to oracle (user=&name pass=&pass path=ExaODIN);
create table DF as select *
from connection to Oracle
(select *
from ODS.collection
where  (  date variable is equal to the current belongs in the current month)
;
disconnect from Oracle;
quit;

[Reeza]

where extract(month from dateVar) = extract(month from current_date) and extract(year from dateVar) = extract(year from current_date)

But are you only concerned with month or do you need to account for the year as well?

Another option is to use TO_CHAR() to convert them to characters and ensure they're the same.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions180.htm

Honestly, these are now Oracle questions, but I happen to work on an Oracle DB so know how to do both. 

[TheNovice]

I understand.. I used this..

%Let today = %sysfunc(date(),date9.);
%let sysmonth= %sysfunc(month("&sysdate"d));
%let sysyear= %sysfunc(year("&sysdate"d));
/*DF Inventory for the month*/

PROC SQL;
connect to oracle (user=&name pass=&pass path=ExaODIN);
create table DF as select *
from connection to Oracle
(select *
from ODS.collection
where extract(month from col_actv_date) = &sysmonth.
)
;
disconnect from Oracle;
quit;

but it's still running so i will try your solution

[SASKiwi]

Please post details of the two tables you are querying and some sample data, made up if necessary. I'm guessing there is some matching by IDs between these tables but you haven't explained what this is. We end up having to guess what you want because you haven't fully described what your input data looks like.

[TheNovice]

so Table 1 has a bunch of variables. I need ID for the current month only). The table is big so i am deciding to use the passthrough code as I had pasted.

The table will go like

ID  actv_Date(datetime format)

1   21Apr2019

2   22Apr2019

3  01Jul2019

4  04Jul2019

so if i was running the code this month it should only pull ID 3 and 4.

The second table is set up the same way as above. it has a variable i need that is based on the ID that i pulled above.

ID         amt         date

1         $22       01jul2019

2         $23        24Jul2019

3         $24      26Jul2019

4         $25      04apr2019

after i pull records for the current month , i would like to pull records from the second table where ID is in first table and the  date (from second table) >= date (from first table)

I know how to do this is in multiple steps but is there a way to do it in the pass through statement. I would ideally like to schedule this in Sas mc so i don't have update anything

[SASKiwi]

I would test a simple solution before trying to use macro variables.

PROC SQL;
connect to oracle (user=&name pass=&pass path=Exa);
create table DF as select *
from connection to Oracle
(select A.*
from table2 as A
inner join
(select ID
       ,max(actv_date) as actv_date_max
 from table1
 where actv_date >= '01-Jul-2019'
 group by ID
) as B
on A.ID = B.ID and A.date >= B.actv_date_max
;
disconnect from Oracle;
quit;

[TheNovice]

This worked as well! I think when i was trying all this last night there was a sas maintenance going on and they didn't inform everyone. Thank you! 

[Reeza]

That's just checking the months are the same? Do you want data from July of 2018 and 2017 as well?

[TheNovice]

 I only want it for the current month in the current year

[Reeza · Accepted solution]

PROC SQL;
connect to oracle (user=&name pass=&pass path=ExaODIN);
create table DF as select *
from connection to Oracle
(select *
from ODS.collection
where extract(month from dateVar) = extract(month from current_date) and extract(year from dateVar) = extract(year from current_date)
)
;
disconnect from Oracle;
quit;

Did this not work? If not, what was the error?
Do you have access to Oracle SQL Developer to test the code ahead of time?

[TheNovice]

It worked!! For some reason it was taking so long last night that i abandoned it. Sorry for the delay in replying and thank you!

[ScottBass]

Why don't you hit the Oracle doc and do all this directly in Oracle?

I don't have Oracle but some Googling:

https://docs.oracle.com/cd/B28359_01/server.111/b28286/functions037.htm#SQLRF00628

https://stackoverflow.com/questions/22060885/how-to-get-current-month-records-from-oracle-since-firs... (trunc function?  I didn't find a MONTH function, although you appear to be using this in your passthrough)

I didn't bother with your 2nd query, but I suspect that could all be done natively with a bit of Oracle fu.

[TheNovice]

I only have access to SAS EG...

I may be confusing everyone  and the previous code suggestions don't work so I am pasting the actual tables with a step by step of what i am trying to achieve. I know how to do it but my way would require manual date changes

i am trying to automate this...