BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
avinashaecwb
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
JuanS_OCS
Amethyst | Level 16

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.

View solution in original post

2 REPLIES 2
JuanS_OCS
Amethyst | Level 16

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.

avinashaecwb
Obsidian | Level 7

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

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.

Discussion stats
  • 2 replies
  • 3116 views
  • 0 likes
  • 2 in conversation