Desktop productivity for business analysts and programmers

SAS Guide - execute sql by string interpretation

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

SAS Guide - execute sql by string interpretation

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!


Accepted Solutions
Solution
‎12-28-2015 05:09 AM
Super User
Posts: 5,372

Re: SAS Guide - execute sql by string interpretation

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.

View solution in original post


All Replies
Solution
‎12-28-2015 05:09 AM
Super User
Posts: 5,372

Re: SAS Guide - execute sql by string interpretation

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.

Respected Advisor
Posts: 4,826

Re: SAS Guide - execute sql by string interpretation

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.

PG
Super Contributor
Posts: 260

Re: SAS Guide - execute sql by string interpretation

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

 

&var_val..table_oracle

 

New Contributor
Posts: 2

Re: SAS Guide - execute sql by string interpretation

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 414 views
  • 3 likes
  • 4 in conversation