|
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;
It looks like you just made something up. The syntax *is* documented.
Try
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;
>
>TIA,
>-PW
|
