SAS Guide - execute sql by string interpretation

[Huhufleisch]

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!

[Astounding · Accepted solution]

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.

[PGStats]

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.

[RahulG]

One problem I can see in your code- there should be double dot after macro variable as shown below

&var_val..table_oracle

[Huhufleisch]

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