DATA Step, Macro, Functions and more

Generic script for automated table creation

Accepted Solution Solved
Reply
Contributor
Posts: 72
Accepted Solution

Generic script for automated table creation

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?


Accepted Solutions
Solution
‎11-25-2014 12:55 PM
Super User
Posts: 17,785

Re: Generic script for automated table creation

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


All Replies
Solution
‎11-25-2014 12:55 PM
Super User
Posts: 17,785

Re: Generic script for automated table creation

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;

Contributor
Posts: 72

Re: Generic script for automated table creation

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.

Super User
Posts: 17,785

Re: Generic script for automated table creation

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.

Contributor
Posts: 72

Re: Generic script for automated table creation

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

grateful.

Regular Contributor
Posts: 168

Re: Generic script for automated table creation

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;

Regular Contributor
Posts: 168

Re: Generic script for automated table creation

Please ignore my question.

Super User
Posts: 17,785

Re: Generic script for automated table creation

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;

Contributor
Posts: 72

Re: Generic script for automated table creation

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

Super User
Posts: 17,785

Re: Generic script for automated table creation

Then my first answer should work Smiley Happy

☑ This topic is SOLVED.

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

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