Will the real column "AGE" please stand up?

2

A colleague asked me to run a certain SAS program and then try to view the output in SAS Enterprise Guide. The output contained an SQL VIEW, and darn it, the application refused to open it, reporting only that the data "could not be opened." There was nothing wrong with the VIEW, said my colleague. PROC PRINT liked it just fine.

I ran a PROC SQL step with a DESCRIBE VIEW statement to see what was so offensive about this particular VIEW that SAS Enterprise Guide wouldn't show it. It was a complicated VIEW with many columns, but it didn't take long to discover the issue. The VIEW contained multiple columns that shared the same name.

This is easy to replicate with a simpler VIEW. For example, try out this SAS program:

proc sql;
  create view dups as
    select height as age,
       weight as age,
       age from sashelp.class;
quit; 
 
proc contents;
proc print; run; /** all ages event! **/
proc print; var age; run; /** which age will show? **/

The first PROC PRINT step fools you by renaming the duplicate columns so that they all appear as unqiue columns in the data ("age", "age2", "age3"). PROC CONTENTS though, shows you the reality: these three columns are named the same. And the second PROC PRINT that specifies the AGE variable to print -- well, that prints just the first AGE in the set. Don't try to specify AGE2 as suggested by the earlier PROC PRINT output; you'll get an error.

Back to SAS Enterprise Guide: it simply doesn't tolerate data with duplicate column names. How would you tell one column from another? It can't answer that question, so it returns its "cannot open" error.

But now that we know about this situation, we can improve things slightly. The next release of SAS Enterprise Guide still refuses to open this data VIEW, but now it will tell you why: at least one column has a duplicate name of "AGE".

Tags SAS tips
Share

About Author

Chris Hemedinger

Senior Manager, SAS Online Communities

+Chris Hemedinger is the manager of SAS Online Communities. Since 1993, Chris has worked for SAS as an author, a software developer, an R&D manager and a consultant. Inexplicably, Chris is still coasting on the limited fame he earned as an author of SAS For Dummies.  He also hosts the SAS Tech Talk webcasts each year from SAS Global Forum, connecting viewers with smart people from SAS R&D and the impressive work that they do.

2 Comments

  1. dear sir
    I want to know that the experiment design was 2 × 4,how to write the process i need.And should i used the glm process or anova process separately ?How to anaylsis interaction between groups?and how to estimate the SEM by sas process?

Leave A Reply

Back to Top