topic Re: Pull data based on current month of date in a second dataset in SAS Programming

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

TheNovice — Tue, 30 Jul 2019 22:01:54 GMT

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

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

Reeza — Tue, 30 Jul 2019 22:11:17 GMT

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.

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

TheNovice — Tue, 30 Jul 2019 22:26:50 GMT

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;

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

Reeza — Tue, 30 Jul 2019 22:34:15 GMT

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.

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

TheNovice — Tue, 30 Jul 2019 22:37:46 GMT

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

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

SASKiwi — Tue, 30 Jul 2019 22:54:15 GMT

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.

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

TheNovice — Tue, 30 Jul 2019 23:05:13 GMT

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

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

Reeza — Tue, 30 Jul 2019 23:41:57 GMT

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

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

SASKiwi — Wed, 31 Jul 2019 01:14:36 GMT

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;

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

ScottBass — Wed, 31 Jul 2019 01:59:20 GMT

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-first-date (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.

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

TheNovice — Wed, 31 Jul 2019 02:19:49 GMT

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

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

TheNovice — Wed, 31 Jul 2019 02:20:20 GMT

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

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

SASKiwi — Wed, 31 Jul 2019 04:47:21 GMT

Did you try my non-macro example? If so, what was wrong?

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

ChrisNZ — Wed, 31 Jul 2019 05:23:33 GMT

@SASKiwi A closing parenthesis is missing in your query.

@TheNovice In case this is not clear:

proc sql;

connect using ORA;

select *

from connection to ORA (

select *

from TABLE1, TABLE1

where ID=ID1

order by ID1

)

order by ID1;

Anything between the parentheses in the query above runs in Oracle and must use Oracle syntax. No SAS functions.

Anything else runs in SAS and must use SAS syntax. No Oracle functions or syntax.

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

Reeza — Wed, 31 Jul 2019 15:18:11 GMT

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?

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

TheNovice — Wed, 31 Jul 2019 15:34:34 GMT

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!

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

TheNovice — Wed, 31 Jul 2019 15:35:38 GMT

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!

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

TheNovice — Wed, 31 Jul 2019 15:50:12 GMT

Thank you @ChrisNZ . First time doing it this way but I will keep this in mind