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

Hello, this is my first time on sas community. I wrote this query to find some guidance on the topic of a generic script on which I'm working upon.

What I wanted to do through that script is to create tables in teradata, source being oracle.

To make it generic for a single table I wrote it like:

%macro job(var);

Proc SQL;

Create table tera.&var as select * from ora.&var;

Quit;

%mend job();

%job(table name );

Where tera and ora are libraries.

What I want to do now is to make this script generic for 60 tables.

I will make a temporary data set in which I will store the 60 table names and want to call those table names in place of variable VAR one by one to create table in teradata.

Can any one please help me?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Look into call execute.

If you're table with names is table_names and variable is table_name

%macro print(data);

proc print data= &data (obs=10);

run;

%mend print;

data table_names;

input table_name $20.;

cards;

sashelp.class

sashelp.cars

sashelp.air

;

run;

data _null_;

set table_names;

call execute("%print("||table_name|| ");");

run;

View solution in original post

9 REPLIES 9
Reeza
Super User

Look into call execute.

If you're table with names is table_names and variable is table_name

%macro print(data);

proc print data= &data (obs=10);

run;

%mend print;

data table_names;

input table_name $20.;

cards;

sashelp.class

sashelp.cars

sashelp.air

;

run;

data _null_;

set table_names;

call execute("%print("||table_name|| ");");

run;

Hercules
Fluorite | Level 6

Can you please elaborate what actually this will do?

I do understand that you are creating a macro print for printing 10 observation. Then a dataset table_names having column as table_name.

But what does sashelp., sascar., cards And sasair. do?

Also the call execute statement?

Sorry for the trouble. It's only 3 weeks for me into sas programming. I tried to understand your logic but couldn't.

Reeza
Super User

I'm generating fake data that I can use to replicate your question. I generate a data set called table_names that has the names of the tables that I want to feed to the macro.  The call execute generates the macro call statements for each value of the table_name in the dataset table_names.

Hercules
Fluorite | Level 6

That awesomely worked. Thank you so much Reeza. Why dint I thought of it !!! :smileysilly:

grateful.

RamKumar
Fluorite | Level 6

Apologies for questioning you on the same subject. When I ran the first part of your code with and without the following macros output looks similar. I don't see the effect of these macros in subsequent steps. Also could you please advise how the call execute produces only ten observations without any explicit definitions over observations?

%macro print(data);

proc print data= &data (obs=10);

run;

%mend print;

RamKumar
Fluorite | Level 6

Please ignore my question.

Reeza
Super User

Or you could just use proc datasets or proc copy to copy the data sets over.

See Base SAS(R) 9.2 Procedures Guide

Assuming the same table as above table_names exists:

proc sql noprint;

select table_name into :extract_list separated by " "

from table_names;

quit;

proc datasets library=ora;

copy out=tera;

select &extract_list;

run; quit;

Hercules
Fluorite | Level 6

Thank you Reeza.

This was helpful.

But what I want to do is, I have created my table structures in teradata with 3 more audit columns.

So basically my proc sql statement will look like :

Proc sql;

Insert into Tera .table_name select *, monotonic(), job_id, datetime() from Ora.table_name ;

I don't think copy out teradata statement will work on this.

Thanks in advance

Reeza
Super User

Then my first answer should work Smiley Happy

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1211 views
  • 3 likes
  • 3 in conversation