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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

4 REPLIES 4
Astounding
PROC Star

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
Opal | Level 21

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
RahulG
Barite | Level 11

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

 

&var_val..table_oracle

 

Huhufleisch
Calcite | Level 5

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

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

Creating Custom Steps in SAS Studio

Check out this tutorial series to learn how to build your own steps in SAS Studio.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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