Date:         Tue, 18 Jul 2006 18:15:38 -0500
Reply-To:     "Gregg P. Snell" <gsnell@DATASAVANTCONSULTING.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Gregg P. Snell" <gsnell@DATASAVANTCONSULTING.COM>
Organization: Data Savant Consulting
Subject:      Re: Create Table Showing Number of Observations in Each Dataset
Comments: To: Paul Walker <walker.627@OSU.EDU>
In-Reply-To:  <200607182250.k6IM7PBb025355@mailgw.cc.uga.edu>
Content-Type: text/plain; charset="us-ascii"

Paul,

Take a look at sashelp.vtable.

Regards,
Gregg Snell

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Paul
Walker
Sent: Tuesday, July 18, 2006 5:51 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Create Table Showing Number of Observations in Each Dataset

I would like to create a table that looks like the following:

  dataset    nobs
  -------    ---------
    D1       10000
    D2       12000
    D3       7500

The column 'dataset' represents the name of a dataset, and nobs is the
number of observations.  Suppose there are 3 datasets as indicated, D1, D2,
and D3.  There are several ways to create such a table using macro variables
or dictionary tables.  However, I would like to use SQL to build this table
in the following sort of way.

proc sql noprint;
  create table work.nobs_in_datasets as
  union a (count(*) as nobs, 'D1' as dataset from work.D1)
        b (count(*) as nobs, 'D2' as dataset from work.D2)
        c (count(*) as nobs, 'D3' as dataset from work.D3); quit;

However, I know the above code does not work and wondered if any PROC SQL
experts could help correct my syntax?  The basic problem is how to 'stack'
the rows of data created by the code:

count(*) as nobs, 'D1' as dataset from work.D1;
count(*) as nobs, 'D2' as dataset from work.D2;
count(*) as nobs, 'D3' as dataset from work.D3;

TIA,
-PW