Date:         Tue, 18 Jul 2006 19:00:28 -0400
Reply-To:     Ya Huang <ya.huang@AMYLIN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ya Huang <ya.huang@AMYLIN.COM>
Subject:      Re: Create Table Showing Number of Observations in Each Dataset
Comments: To: Paul Walker <walker.627@OSU.EDU>

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


Bear in mind that this can only go up to 16 data sets.


On Tue, 18 Jul 2006 18:50:42 -0400, Paul Walker <walker.627@OSU.EDU> wrote:

>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