How to describe a table

[deleted_user]

In EG, can I use code, maybe some SQL, to describe the layout of a table? I'm looking to be able to list all the fields in several tables, but haven't seen anything about it.

Any help is definitely appreciated!

[Cynthia_sas]

Hi:
EG provides you with the List Data task. In the background it is just using a proc print, I think. You can accomplish the same thing several ways with code:
[pre]

proc sql;
select * from sashelp.class;
quit;

proc print data=sashelp.class;
run;

proc report data=sashelp.class nowd;
run;
[/pre]

Proc Print will only give you a "detail" report -- listing all of the observations in a SAS dataset. You can control the rows that you see by adding on a where statement, and, you can add totals for numeric variables, but you see every row in the dataset or subset. By default, PROC REPORT is also going to give you a detail report, where you see every row in your dataset or subset.

So, if what you meant by "describe a table" as list out all the observations, then you would use the above procedures, depending on what you wanted to see.

If on the other hand, you want to see ONLY the column names and other information about the data file, you can use PROC CONTENTS or PROC DATASETS. I prefer PROC CONTENTS:
[pre]
proc contents data=sashelp.class;
run;

proc contents data=sashelp.shoes;
run;

OR to see information on all the tables in a SAS library:

proc contents data=sashelp._all_;
run;
[/pre]

Will either of those examples do what you want?
cynthia

[deleted_user]

Hi, Cynthia,

And thank you for your reply.

This is what I used for code, but got an error message that said, "WORK.EMPLOYEE.DATA does not exist":

libname ODSP oracle path=odsp schema=odsmgr user=ods_view2 password = "#*695ODS.";
%stpbegin
proc contents
data=employee;
run;
%stpend

What did I do or not do incorrectly?

Thanks again for your help!

[Cynthia_sas]

Hi!
Well, a one level name [pre]
data=employee
[/pre]

means that SAS should look in the Work library on either the workspace server or the stored process server. Is the table named employee actually IN the work library or is it in the ODSP library that you're pointing to with your LIBNAME statement??
[pre]
proc contents data=ODSP.employee;
run;

** OR;

proc contents data=ODSP._ALL_;
run;
[/pre]
OR, you might try this -- actually making employee table in WORK:

[pre]
libname ODSP oracle path=odsp schema=odsmgr user=ods_view2 password = "#*695ODS.";
%stpbegin;
** creates work.employee;
** put your table name where I have ?????;
** and put a valid where clause for your data (so you do not have to wait for a long subset to be built);
data work.employee;
set ODSP.?????;
where lastname contains 'Smith';
run;

proc contents data=employee;
run;
%stpend;
[/pre]

Now, you have explicitly created work.employee, so your proc contents should find work.employee (assuming that you pick the right TABLE name and the right where clause. Also, I am not very good with the oracle libname engine -- are you sure you have the right form of the LIBNAME statement for Oracle?

And, last but not least, since I am an instructor, it would just make me happier to see
%stpbegin;
and
%stpend;
(with semicolons after each call to the stored process macros).

To make sure you have code that works, try this:
[pre]
%stpbegin;

data work.shoes;
set sashelp.shoes;
where Region = 'Asia';
run;

proc contents data=work.shoes;
run;
%stpend;
[/pre]

and register that as a stored process. If you run into any stumbling blocks along the way or need help with Oracle libname statement or writing a stored process that creates work files, Tech Support, as always, is your best bet for a definitive answer given your particular configuration and your metadata setup and you SAS/Access to Oracle configuration.

cynthia

[deleted_user]

Hi, Cynthia,

IT's actually in my ODSP library. Looks like my problems are my own perception of how to do things in SAS and EG; I am quite new to SAS, and am learning as fast as I can!

I'll give your latest suggestions a shot in the morning, and thnaks again!!

[Cynthia_sas]

Hmmm, think of it this way...sort of 3 levels:

The fundamental architecture of Base SAS is composed of the proprietary data structure for SAS datasets and the procedures and programming language which represent the code way to "do" things (like access the SAS data sets or access Oracle tables, run a regression on SAS data (or Oracle data that is being treated like SAS data), or produce a graph or generate a summary report, etc, etc).

EG is a way to generate SAS results without needing to know how to "do" things with code -- because EG writes code for you based on choices you make in the EG GUI interface. (There are other GUI interfaces or front-ends that generate code or let you visually explore your data. EG is just one of them.)

The BI Platform is a way to deploy the things you "do" across an entire enterprise so that report users/end users/report consumers don't have to know either SAS or EG. The beauty of the BI platform is that in the "old" days, you had to execute some code (PROC EXPORT, ODS CSV, ODS HTML) in order to create files from SAS that Excel could open. Under the BI Platform, Excel "knows" how to access a SAS data source because the data source is described in the metadata and Excel "knows" how to read the metadata. (And, not just Excel, but Word and PowerPoint, too).

No matter which of those 3 "levels" you're at, one of the basic tenets of SAS is that SAS data files live in SAS libraries. Your SAS libraries have names -- they have physical path names and they have short nicknames (called librefs). So THIS Libname statement:
LIBNAME MYLIB 'c:\temp';
is telling SAS that on a physical C drive, in a subdirectory called temp, is a SAS library whose nickname (or libref) is called MYLIB. If you went out to c:\temp, with Windows Explorer, for example, you could tell that SAS data files were there if you saw files with the extension .sas7bdat (for SAS 8 and SAS 9). So let's say that I have these 2 files out in c:\temp:

c:\temp\gifts.sas7bdat
c:\temp\grants.sas7bdat

OK...the libname statement is assigning a "nickname" to c:\temp -- so instead of needing to know the physical path to the data, I can refer to the data files by their 2-level "internal" SAS names:
mylib.gifts
and
mylib.grants

Why don't I need the .sas7bdat file extension in the name? Well, SAS knows that these file extensions belong to SAS, so they're not necessary. So, what about your Oracle tables. I don't actually know what the equivalent Oracle file extension would be on an Oracle server. But it doesn't really matter. The nickname/libref ODSP will point to the Oracle table(s) that can be found in the path that is specified on the Libname statement using the Oracle engine. So if you have a table called EMPLOYEE in your Oracle database AND (a big AND) you have the syntax of your LIBNAME statement correct, then you should be able to point to the Oracle table in SAS with this 2 level name:
ODSP.EMPLOYEE

So, what is the WORK library for? EG uses the WORK library a LOT. But files don't go into the WORK library automatically. They have to be put there with SOME bit of code. So if you ever look at the code that's generated by EG and you see this:
[pre]
proc sql;
create table WORK.WOMBAT as
select name, age, height
from sashelp.class
where age gt 13;
quit;
[/pre]

Then, that means that EG is making a temporary copy of the SASHELP.CLASS file, calling that temp file WORK.WOMBAT (although EG picks far more interesting names than WOMBAT) and is only keeping the variables/columns name, age and height in the work copy of the file. AND because the SELECT statement has a WHERE clause, the temporary copy of the SAS table will only contain those rows where AGE was GT 13.

Anytime you see a 1 level name in SAS code (which isn't very often in EG)
[pre]
proc sql;
create table KOALA as
select name, age, height
from sashelp.class
where age gt 13;
quit;

proc contents data=KOALA;
run;
[/pre]
you are essentially instructing SAS to look in the temporary WORK library for a table. In the code above, the PROC SQL step is creating WORK.KOALA and PROC CONTENTS is showing the dataset/table information for WORK.KOALA. If, for some reason the PROC SQL failed and WORK.KOALA was NOT created, then your PROC CONTENTS might also fail if WORK.KOALA was not found in the WORK library.

I could just as easily create a WORK file this way:
[pre]
data newgift;
set sashelp.class;
if age le 13 then do;
giftidea='game';
giftamt = 20;
end;
else if age gt 13 then do;
giftidea='book';
giftamt = 25;
end;
run;

proc print data=newgift;
title 'Gift Ideas for SASHELP.CLASS folks';
run;
[/pre]

And, then to make the "newgift" table permanent I only need 2 things:
1) a permanent LIBNAME statement
libname mylib 'c:\temp';
2) change the DATA statement to:
data mylib.newgift;

(which also means that proc print statement would have to change to data=mylib.newgift)

Things get a little more complicated when you're dealing with stored processes and writing files to the WORK library on the stored process server or the workspace server because the WORK library is very temporary and goes away as soon as the stored process has finished executing. So there is no visibility or persistence of the WORK library from one stored process to another. When you're inside a BASE SAS session with Display Manager or inside an EG session, the WORK library persists until you close the session or close EG.

So, if you did something like this in one stored process: [pre]
%stpbegin;
LIBNAME MYORA Oracle...;

proc sql;
create table work.wombat as
select *
from myora.oratab
where var1 = 'wombat';
quit;

proc print data=work.wombat;
run;
%stpend;
[/pre]
and then did something like this in a second stored process:
[pre]
%stpbegin;
proc freq data=work.wombat;
tables var1;
run;
%stpend;
[/pre]

the WORK file would NOT be there when the second stored process executed. You'd have to move the PROC FREQ step into the first stored process or make the WOMBAT file persist by assigning it to a permanent library on the server.

And things are a little MORE complicated in your case, because once you are dealing with the metadata repository, then you frequently do NOT use LIBNAME statements with physical path names because the physical location of the data and the type of library (ODBC, ORACLE, DB2) are already defined in the metadata -- so instead of this:

libname MYLIB 'c:\temp';

you might have this:

libname MYLIB meta port=8561 library='My Personal Library' repository='Foundation';

but the 2 level name mylib.employee or mylib.newgift would be the same. The only difference is how you set up the nickname -- whether you use a physical path or whether you go through the metadata repository definition of the table (the preferred method -- because the metadata also contains access and security permissions for the library and its tables).

So, more information about LIBNAMES than you wanted to have with your morning coffee, huh?

cynthia

[deleted_user]

!!! What a LOT of information to digest!!! Thank you so much for such a detailed reply! I'll try to digest it today and Monday.

And again, thank you for your knowledgable assistance.