Hi Art, Thank you so much for your assitance  and all who responded . I was able to fig out enclose is what I use  data _null_; call symput('yesterday', "'"||put(today()-1, mmddyy10.)||"'"); run; proc sql; CREATE TABLE Part1 AS select * from connection to ODBC (SELECT Date, CodeNumber, Sum(Amount) AS ReplenAmount FROM tbVendors where DATE =&yesterday GROUP BY Date, cOdeNumber,Amount ORDER BY CodeNumber); Run; ... View more

Here is my code  with errors message.. The 1st table works as the data step. The issue is when  to select today date minus 1 or 3 options symbolgen; %LET START = TODAY(SYSDATE,'DD-MON-RR') -3; proc sql; CREATE TABLE Part1 AS select * from connection to ODBC (SELECT Date, CodeNumber, Sum(Amount) AS ReplenAmount FROM tbVendors where DATE > {ts'2012-11-04 00:00:00'} GROUP BY Date, cOdeNumber,Amount ORDER BY CodeNumber); Run; data Part1; set Part1; date=datepart(ReplenDate); format date MMDDYY8.; run; proc sql; CREATE TABLE First AS (SELECT Date, CodeNumber, Sum(Amount) AS ReplenAmount FROM Part1 Where Date >= &START ); Run; ERROR: Function TO_DATE could not be located. ERROR: Expression using subtraction :smileyminus: requires numeric types. ERROR: The following columns were not found in the contributing tables: SYSDATE ... View more

Hi PGStats, Thank you so much for your assistance with my question... BTW I fig out how to reduce the time of the import from 1hr to 7 min.. I went into the table an change all the text columns in access to 'Unicode compress" ....  How can I select that the question has been answer? Thanks a bunch ... View more

     PGSTATS, How do I select for correct answer ? ... View more

Thanks PG Stats for all your help ... View more

Part1)This takes 1 hr on avg to pull the table has 500k records. When the table gets created with the 30 filter it produces 23k records procsql; connectto Accessas db (path="g:\Shared Databases\CCMS 2\CCMS2Production.mdb"); createtable myDataas select *from connection to db (select * from [tbl_deposit] where tbl_deposit.Scheduled_Settle >= int(now())-30 ); quit; 2nd Part) If I want to add an additional criteria " And Repeat >1"  is just an "AND"? SAS is a funny program how would  I add wild card too pick up all types of False(I.E false,False,FALSE) procsql; createtable depcountas select Terminal_ID,Count(Terminal_ID)As Repeat from Mydata Where ON_SCHEDULE ="FALSE" groupby Terminal_ID; quit; tHANKS AGAIN PGSTATS ... View more

Hi PGStats, I change it to table from view an now its avg about 1hr to pull data. I might have to learn to live with that unless you have any suggestion it has over 500k records an grows everyday  a little... I tried creating a count of all the TID that have "false"  an it states remerging  what do you think is causing that?                   174   proc sql;   175 create table depcount as   176 (select   177 Terminal_ID,   178 Count(distinct   Terminal_ID) As Repeat   179 from Mydata   180 Where ON_SCHEDULE eq   'False'   181 )   182 ;   NOTE: The query requires   remerging summary statistics back with the original data.   NOTE: Table WORK.DEPCOUNT   created, with 15 rows and 2 columns.                 ... View more

proc sql; 33   create table depcount as 34 (select 35   Terminal_ID, 36   Count(Terminal_ID) As Repeat 37   from Mydata i Hi PG STATS,The [] worked the issue is when I try to create a simple table based of  the Mydata table(view). I get a error I enclosed error log.  Since its a view could I use it like a normal table in SAS? or is it limited ? Thanks again 38   Where ON_SCHEDULE eq 'False' NOTE:   This SAS session is using a registry in WORK. All changes will be lost at the   end of this session. NOTE:   This SAS session is using a registry in WORK. All changes will be lost at the   end of this session. ERROR:   Maximum level of nesting of macro functions exceeded. ERROR:   Maximum level of nesting of macro functions exceeded.                 ... View more

Hi PG Stats I plug in your code an  did  test run on a access db I 'v on my desktop an it worked great. When I tried it on the real Access db I rec this error.Does it matter type of access it was to a .mdb not a accdb? The "Settle" is in a date/ time format dd/mm/yy 00:00:00. The  real Path is to a shared directory ERROR: Prepare: Syntax error in query.  Incomplete query clause. SQL statement: select * from "tbl_dep" where tbl_dep.Settle        >= int(now())-30 ERROR: SQL View WORK.MYDATA could not be processed because at least one of the data sets, or views, referenced directly (or indirectly) by it could not be located, or opened successfully. Thanks Again for all your help ... View more

Is there a way in doing the libname I can filter for only last 30 days worth of data?  Where would I insert ACCESS=READONLY and DEFER=YES what I hV E NOW IS " libname Access c:\ Complinance.mdb;" ... View more