I'm very new at SAS Guide, and I didn't find nothing to solve my problem.
I need to do a PROC SQL that inserts a few data into a sas table from an oracle table.
It works fine for many tables. But I have some tables that are repeated in some schemas and I would like to reuse the same program, inputing the schema as a param.
Example:
PROC SQL; SELECT VAL INTO:var_val FROM PARAM WHERE DESCRIPTION = 'somevalue'; CONNECT TO ORACLE (PATH=ORACLEDB USER=XXX PASSWORD=YYY); INSERT INTO MYTABLE (ColumnA) SELECT ColumnA FROM CONNECTION TO ORALCE( SELECT ColumnA FROM &var_val.table_oracle ); QUIT;
It doesn't work because variable &var_val is substituted with apostrophes. I hope that exists something like an "EXEC" command that I could tranform my query in a complete string, and execute after substituation.
Is there some way to do what I need?
thks!
It should be possible with a little tweaking to the macro variable. The basic questions (at least to start):
1. What is actually in &VAR_VAL?
2. What would the correct syntax look like after text substitution has been made, in the final FROM clause?
Depending on your answers, it might be as simple as adding another dot (&var_val..table_name).
Also note that there may be other pieces that could be automated but first let's get the error cleaned up.
It should be possible with a little tweaking to the macro variable. The basic questions (at least to start):
1. What is actually in &VAR_VAL?
2. What would the correct syntax look like after text substitution has been made, in the final FROM clause?
Depending on your answers, it might be as simple as adding another dot (&var_val..table_name).
Also note that there may be other pieces that could be automated but first let's get the error cleaned up.
Look at fonction dequote to remove unwanted quotation marks (single or double) from string VAL. Use option symbolgen to see the substituted macro strings in the code.
One problem I can see in your code- there should be double dot after macro variable as shown below
&var_val..table_oracle
Thanks for everyone, using double dots works fine.
It's a little hard when you don't know what you should look up.
I'm using this documentation for understand how this works:
https://support.sas.com/resources/papers/proceedings13/120-2013.pdf
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.