how to dynamically form exec() in PROC SQL?

Reply
Occasional Contributor
Posts: 5

how to dynamically form exec() in PROC SQL?

I want to have the sas code like

PROV SQL;

CONNECT TO ODBC(...);

EXEC(ALTER TABLE xxx  &ADD_Columns_String) BY ODBC;

...

Where &ADD_Columns_String = ADD COLUMN A, ADD COLUMN B; the columns might be  varying each time.

How can I achieve this?

Thanks

Super User
Super User
Posts: 7,401

Re: how to dynamically form exec() in PROC SQL?

You could put your proc sql into a macro:


%macro DO_SQL (ADD_COLUMNS_STRING=);

PROV SQL;

CONNECT TO ODBC(...);

EXEC(ALTER TABLE xxx  &ADD_Columns_String.) BY ODBC;

%mend DO_SQL;

%DO_SQL(ADD_COLUMNS_STRING=%str(COL1,COL2));


Note however that SQL uses comma delimited lists and if you put a comma in the macro parameter it will separate that out and give an error, hence put the %str.  You could also use call execute.

Super User
Super User
Posts: 6,499

Re: how to dynamically form exec() in PROC SQL?

If the list of columns is in a dataset you can build it using an SQL select. Note that total string must fit into a macro variable so there will be limit on how many variables you can use.

proc sql noprint ;

select catx(' ','add column',name)

  into :add_columns_string separated by ','

  from VARIABLE_LIST

;

connect to odbc(...);

exec(alter table xxx  &add_columns_string) by odbc;

But why add columns to an existing table???  How will they get populated?  Why not just create a new table with the columns and the data that you want? 

Ask a Question
Discussion stats
  • 2 replies
  • 259 views
  • 0 likes
  • 3 in conversation