Resolve Macro Variable At Execution within PROC SQL VIEW

[SASMike]

Is it possible to resolve a macro variable within a PROC SQL view at execution time?  I have a query with a dynamic FROM reference and I'd like to create a view without the current value of that macro being stored with the definition of the view.  I've tried SYMGET() to no avail.

Here's my example.  I'd like to be able to change MYMACROVARIABLE from table1 to table2 without having to redefine the view.

PROC SQL;

      CREATE VIEW test AS

             SELECT *

             FROM &MYMACROVARIABLE;

QUIT;

Any feedback would be appreciated! 🙂

Thanks!

[LinusH]

I can't think of way of doing this from the top of my head.

And that seems reasonable - since the view, like a table, must contain columns with data types, labels and formats. Can't see how that can work "out of the box" by allowing dynamic change of part of the SQL view code.

But tell us a bit about your application/requirement, perhaps there another option to solve your problem.

[Quentin · Accepted solution]

I wouldn't think so. When that CREATE VIEW statement executes, I think it needs to be a complete statement, including the FROM clause.

You could of course generate the statement in a macro, something like:

%macro makeview(view,from=);
      CREATE VIEW &view AS
             SELECT *
             FROM &from
      ;
%mend ;

%let myMacroVariable=sashelp.class;

PROC SQL;
      %MakeView(Vclass,from=&MyMacroVariable)
      %MakeView(Vshoes,from=sashelp.shoes)
QUIT;

[SASMike]

Thanks Quentin. 🙂 I agree. I'll have to create a work-a-round with a macro.

[LinusH]

This will probably work just fine in a single user environment. But if it is, why the need to create a view in this dynamic fashion.

In a multi user/application environment, you probably need some other mechanism to make data available - creating permanent views can be a really mess without proper maintenance/architecture.

[RW9]

Hi,

Could you give a scenario where you would need to create a view on a table you don't yet know?  Seems a bit counterintuitive.  A view is a picture of the data, so if you don't know the data, why or how would you be able to create a picture of it?