I have a data query created using ODBC tables. These tables get updated evry few minutes. How can I ensure that the data query used in SAS always fetches latest data?
1) I can Schedule the query with "daily" frequency. In this case, what can I do to make sure only updated and/or newly added rows are only refreshed in the SAS data set in LASR ?
2) I can schedule the tables to auto load using ‘schedule.bat’ or ‘schedule.sh’ .I am new to SAS so can you guide on the code for these two files? Also, can this too be setup to make sure only updated and/or newly added rows are only refreshed in the SAS data set in LASR ?
3) Is there any other better method?
Thanks !!
Hi @avinashaecwb,
that is a nice question.
On SAS VA, you can use the autoload only with already generated SAS tables (physical), csv, excel or delimited files. Not with database tables.
But as far as this is a query created with Data Builder, you can schedule (on the operating system, or with the Data Query Builder itself if you have the right roles for Scheduling, within the SAS Management Console) this query to run on the frequency of your election. I would strongly recommend to run queries against databases on periods of low user activity, otherwise your performance will decrease for both your users and the queries.
Also, if performance is an important matter for you, the re are several ways to increse the performance, such as generate your query externally of your VA server, or even on your database server itself, and just use autoload, once you have a "plain" SAS table or csv.
Hi @avinashaecwb,
that is a nice question.
On SAS VA, you can use the autoload only with already generated SAS tables (physical), csv, excel or delimited files. Not with database tables.
But as far as this is a query created with Data Builder, you can schedule (on the operating system, or with the Data Query Builder itself if you have the right roles for Scheduling, within the SAS Management Console) this query to run on the frequency of your election. I would strongly recommend to run queries against databases on periods of low user activity, otherwise your performance will decrease for both your users and the queries.
Also, if performance is an important matter for you, the re are several ways to increse the performance, such as generate your query externally of your VA server, or even on your database server itself, and just use autoload, once you have a "plain" SAS table or csv.
Thanks @JuanS_OCS . Could you please help me some more by clarifying "..such as generate your query externally of your VA server, or even on your database server itself, and just use autoload, once you have a "plain" SAS table or csv. ...".
1) Do you mean generating query on Oracle SQL Server or Toad or some similar tool and saving the result in a table. Then that table can be set to autoload ? If yes, then even this way how do i ensure that the table is always updated ? We still would need to refresh the query in SQL again and again.
2) Or, I should create the table in Oracle database using Proc SQl and Libname reference to Oracle Db and then use Date Set SAS options to copy the data to SAS LASR. all this should be written in unload and reload scripts in .sas file and put for scheduling as BASE SAS job through SAS management console ?
Sorry if the question is silly but like I said, I have just started with SAS after working as SQL developer.
Thank you.
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 25. Read more here about why you should contribute and what is in it for you!
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.