Read from a database table and write the result in AUTOLOAD folder

[pedrammobedi]

Here is what I am intending to do and I will appreciate if I get a concrete answer and not general statements.

The data I need to load into my SAS VA as a data source is the entire contents of a specific table. Let us assume that I have access to the dastabase using an ODBC connection on my machine. Therefore:

1. Read the data from the database table using a simple SELECT query or something similar
2. Create a dataset from the result of the query above. The dataset should not be created in the WORK library
3. Save the newly generated dataset in SAS VA's AUTOLOAD folder so that we always have as fresh set of data.

P.S. I am sorry if I sound basic. I am relativelty new to SAS. Outside this realm, this is quite a normal scenario. I have used QlikView before. But I understood that it is not very straightforward in SAS.I have only taken the SAS VA course but apparerntly everything is connected and I have to learn all related products to feed the program. Thanks for you help in advance.

[LinusH]

Have you seen this?

http://support.sas.com/training/tutorial/gel/gelva07.html

http://support.sas.com/documentation/cdl/en/vapdbgwin/68972/HTML/default/viewer.htm#n0g0cxn6tnn9yqn1...

[pedrammobedi · Accepted solution]

My question is not about building and setting up the AUTOLOAD folder in VA . The folder is already setup and datasets from other parts of my company are already there and read into VA.

I need to create my dataset there, too. This is what I have come so far for more clarity:

In SAS, I am writing the following base code (MY_CONNECTION contains the connection to my db):

LIBNAME MYLIB ODBC noprompt= "MY_CONNECTION";
DATA NEW;
SET MYLIB.ReportLG;
RUN;
PROC SQL;
CREATE TABLE NEW AS SELECT * FROM MYLIB.ReportLG;
QUIT;

Problem is, the results are created in the temporary WORK folder. How can I change the code so that the resulting dataset shows up in e.g. C:/MyFolder?

[LinusH]

libname autoload "C:\MyFolder";

[pedrammobedi]

And how does this create the result from the SELECT query above to this folder? Could you please paste the code? Basically I want to copy the result of an sql to the autoload location.

[JuanS_OCS]

If performance is important in your organization on the VA server, I would never use the SAS VA server itself. I would use a different SAS server to execute this and move the resulting SAS table to the VA server (autoload).

Leaving that quick remark apart for now, if the libname is named autoload or X, you would just use this as prefix on the table:

DATA X.NEW;
SET MYLIB.ReportLG;
RUN;
PROC SQL;
CREATE X.TABLE NEW AS SELECT * FROM MYLIB.ReportLG;
QUIT;

Also, are you aware of the SAS VA Data Builder web app within SAS VA, right?

With the Query builder you can create your own query (from a database or sas table), create an output table and schedule it automatically on the server to run and load the data into LASR.

[pedrammobedi]

It would be awesome if I could eliminate the middle man and only use SAS VA. I have now loaded my table into the Data Builder. I just don't see the clock icon to schedule it. Could you please help me find it?

[JuanS_OCS]

If you don't have the clock, probably you are missing capabilities roles.

Check that you have (on the SAS Management Console) all the roles related to Scheduling and advanved features for VA.

[pedrammobedi]

Oh sorry. I see the clock but still disabled. I play around with it a bit. You answer is the most relevant I have found to my question. I will come back to you if I had other questions.

[pedrammobedi]

I had a question about scheduling a query in VA. Do you think I can post it here or should I create a new question. You answer here on Data Builder has brought me very close.

[JuanS_OCS]

Of course, go ahead and shoot

[pedrammobedi]

Thank you! I posted the question here: https://communities.sas.com/t5/SAS-Visual-Analytics/Scheduled-data-query-not-updating-the-data-sourc...

[JuanS_OCS]

Hi @pedrammobedi,

may I ask you what is the marked solution actually answers from the initial question?

The reasons:

- If someone have the same problem as you did, they will be able to find the solution more quick and clear.

- I think that @LinusH answer or mine actually are the answers, which made move forward the initial question.

This is totally up to you, and I am not intending to be "harsh" with you, just trying to help trying to explain both the practical and feelings points of view.

What I would do under your situation, on this and other posts:

Mark the post that better answers your question. Or if you want to select yours as a solution, this is fine, as long as in includes the relevan information. Therefore you can mark one post of yours as solution if you summarize the solution, for incoming colleagues looking for a solution.

Thank you in advance!

Best regards,

Juan

[pedrammobedi]

Hi! Thanks for the advice. I just found your answer more closer to what I was looking for. I needed to schedule a query in SAS VA. If you think the question and the accepted solution do not go well please let me know. It is true that the selected solution is not exactly the answer but the comment you have added after your code snippet actually gets me much close to the solution.