topic Re: Resolve Macro Variable At Execution within PROC SQL VIEW in SAS Programming

Resolve Macro Variable At Execution within PROC SQL VIEW

SASMike — Mon, 25 Apr 2016 19:27:12 GMT

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!

Re: Resolve Macro Variable At Execution within PROC SQL VIEW

LinusH — Mon, 25 Apr 2016 20:08:39 GMT

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.

Re: Resolve Macro Variable At Execution within PROC SQL VIEW

Quentin — Mon, 25 Apr 2016 20:11:34 GMT

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;

Re: Resolve Macro Variable At Execution within PROC SQL VIEW

SASMike — Mon, 25 Apr 2016 20:16:15 GMT

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

Re: Resolve Macro Variable At Execution within PROC SQL VIEW

LinusH — Tue, 26 Apr 2016 06:19:33 GMT

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.

Re: Resolve Macro Variable At Execution within PROC SQL VIEW

RW9 — Tue, 26 Apr 2016 07:51:08 GMT

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?