BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASMike
Obsidian | Level 7

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
Super User

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;
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

View solution in original post

5 REPLIES 5
LinusH
Tourmaline | Level 20

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
Quentin
Super User

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;
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
SASMike
Obsidian | Level 7
Thanks Quentin. 🙂 I agree. I'll have to create a work-a-round with a macro.
LinusH
Tourmaline | Level 20

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
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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