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
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?
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;
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.
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
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.
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
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;
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!
I only want it for the current month in the current year
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?
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!
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.
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...
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: