01 Jun

SAS: Views

This post will be similar to the earlier one on indexes.  Views look fairly clean in SAS and, for the most part, I think I know what is going on here, so I’m only going to hit parts where I feel some doubt, however small.  As such, this won’t be the most useful post for someone, who isn’t me, to read.

Creating a view in SAS is pretty much like every other view you’ve created.

libname mysql '....';

ods listing;

proc sql;
	SELECT name, age, sex, height, weight
        FROM mysql.admit;

proc sql outobs=5;
SELECT * FROM myview;

Name                 Age  Sex    Height    Weight
Murray, W             27  M          72       168
Almers, C             34  F          66       152
Bonaventure, T        31  F          61       123
Johnson, R            43  F          63       137
LaMance, K            51  M          71       158

You can display the definition of a view by using DESCRIBE VIEW which will write the view’s definition to the log.

proc sql;

28         DESCRIBE VIEW myview;
NOTE: SQL view WORK.MYVIEW is defined as:

        select name, age, sex, height, weight
          from MYSQL.ADMIT;

SAS offers up a handful of suggestions on how, and when to use a view.

  • They recommend against using an ORDER BY (you can do it) because other users of the view may want to sort it differently.
  • If the data is reused, a lot, they recommend creating a table instead of a view.
  • If the table structure changes a view can become unusable.

You may have noticed that I did not use a LIBREF in the view examples I created earlier.  SAS, actually, recommends that you do that if the view resides in the same resides in the same library as the table.  In my case, they’re both in WORK.

You can assign a LIBREF in the view and not use a separate LIBREF statement.  For example:

proc sql;
	SELECT name, age, sex, height, weight
    FROM mysql.admit
	USING LIBNAME mysql '....';

I think just knowing that, that can be done, would be enough for the test.

You can, just like in a regular database, UPDATE, DELETE or INSERT into a view, in the right situations. Per SAS,

  • You can only update a single table in a view.  You cannot update a query that has a join or a subquery.
  • You cannot update a derived column.
  • You can update a view with a WHERE clause.  The book doesn’t state this but I would assume that inserts would also be restricted by the where clause, which will act like a constraint in a view.
  • You cannot update a view that has a HAVING, ORDER BY or GROUP BY clause.

You get rid of a view the old-fashioned way.

25         DROP VIEW myview;
NOTE: View WORK.MYVIEW has been dropped.

One last note, you cannot join a view.

I’m kind of scared when it comes to views because the book’s chapter on them is really short. I’m afraid that I’m missing things. At the same time, this book has a different feel to it compared to the SAS Base exam. Base felt really petty, even in the book’s sample questions, but I get the sense that the scope here is large enough that they “may” not hammer as much minutiae on this exam.

I guess I’ll have to take it to find out.

Leave a Reply

Your email address will not be published. Required fields are marked *