topic Re: SAS Guide - execute sql by string interpretation in SAS Enterprise Guide

SAS Guide - execute sql by string interpretation

Huhufleisch — Thu, 24 Dec 2015 12:56:48 GMT

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!

Re: SAS Guide - execute sql by string interpretation

Astounding — Thu, 24 Dec 2015 13:43:12 GMT

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.

Re: SAS Guide - execute sql by string interpretation

PGStats — Fri, 25 Dec 2015 04:58:51 GMT

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.

Re: SAS Guide - execute sql by string interpretation

RahulG — Mon, 28 Dec 2015 03:37:26 GMT

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

&var_val..table_oracle

Re: SAS Guide - execute sql by string interpretation

Huhufleisch — Mon, 28 Dec 2015 10:16:54 GMT

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