DATA Step, Macro, Functions and more

Resolve Macro Variable At Execution within PROC SQL VIEW

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Resolve Macro Variable At Execution within PROC SQL VIEW

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! Smiley Happy

 

Thanks!

 


Accepted Solutions
Solution
‎04-25-2016 04:16 PM
PROC Star
Posts: 1,322

Re: Resolve Macro Variable At Execution within PROC SQL VIEW

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;

View solution in original post


All Replies
Super User
Posts: 5,424

Re: Resolve Macro Variable At Execution within PROC SQL VIEW

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.

Data never sleeps
Solution
‎04-25-2016 04:16 PM
PROC Star
Posts: 1,322

Re: Resolve Macro Variable At Execution within PROC SQL VIEW

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;
Occasional Contributor
Posts: 12

Re: Resolve Macro Variable At Execution within PROC SQL VIEW

Thanks Quentin. Smiley Happy I agree. I'll have to create a work-a-round with a macro.
Super User
Posts: 5,424

Re: Resolve Macro Variable At Execution within PROC SQL VIEW

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.

Data never sleeps
Super User
Super User
Posts: 7,942

Re: Resolve Macro Variable At Execution within PROC SQL VIEW

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?

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 463 views
  • 3 likes
  • 4 in conversation