topic Re: Generic script for automated table creation in SAS Programming

Generic script for automated table creation

Hercules — Tue, 25 Nov 2014 17:34:24 GMT

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?

Re: Generic script for automated table creation

Reeza — Tue, 25 Nov 2014 17:55:34 GMT

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;

Re: Generic script for automated table creation

Reeza — Tue, 25 Nov 2014 17:59:18 GMT

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;

Re: Generic script for automated table creation

Hercules — Tue, 25 Nov 2014 18:22:33 GMT

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

Re: Generic script for automated table creation

Reeza — Tue, 25 Nov 2014 18:27:42 GMT

Then my first answer should work

Re: Generic script for automated table creation

Hercules — Wed, 26 Nov 2014 01:33:17 GMT

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.

Re: Generic script for automated table creation

Reeza — Wed, 26 Nov 2014 01:55:43 GMT

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.

Re: Generic script for automated table creation

Hercules — Wed, 26 Nov 2014 10:01:44 GMT

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

grateful.

Re: Generic script for automated table creation

RamKumar — Wed, 26 Nov 2014 11:37:39 GMT

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?