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

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
pedrammobedi
Quartz | Level 8

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?

View solution in original post

13 REPLIES 13
pedrammobedi
Quartz | Level 8

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
Tourmaline | Level 20

libname autoload "C:\MyFolder";

Data never sleeps
pedrammobedi
Quartz | Level 8
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
Amethyst | Level 16

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
Quartz | Level 8
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
Amethyst | Level 16

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
Quartz | Level 8
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
Quartz | Level 8
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
Amethyst | Level 16

Of course, go ahead and shoot

JuanS_OCS
Amethyst | Level 16

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
Quartz | Level 8
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.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 13 replies
  • 2818 views
  • 3 likes
  • 3 in conversation