Date: Thu, 15 Jul 2004 16:54:27 -0400
Reply-To: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject: Re: selecting common variables
Content-Type: text/plain
Laxxy:
The LIKE clause creates an empty table shell (no observations). Think of it
as a framework of column headings for a table. The first three queries
create three empty tables (shells). The third query selects the common
column headings and expands column lengths to the maximum required. All of
what happens up to the beginning of the fourth query remains independent of
the contents of the database and operates exclusively on metadata.
The fourth and fifth queries append each of the data tables separately to
the shell created by the third query. The number of columns in each reduces
to the common subset of column names, but the number of rows matches the
number of rows in the source tables.
The log looks OK so far. The yield of the third query has the number of
columns in the common subset of the two source tables.
Sig
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of laxxy
Sent: Thursday, July 15, 2004 3:49 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: selecting common variables
Thanks a lot!! But I'm having a problem with this code :( Sadly I'm not
really familiar with sql, but here is an excerpt from the sas log file:
58242 proc sql;
58243 create table test1a like test1;
NOTE: Table WORK.TEST1A created, with 0 rows and 4 columns.
^^^^^^^^^^^^^^^^^^^^^^ it does not seem to have any observations :(
58244 create table test2a like test2;
NOTE: Table WORK.TEST2A created, with 0 rows and 4 columns.
58245 create table test3 as select * from test1a union all corr
select * from
58246 test2a;
NOTE: Table WORK.TEST3 created, with 0 rows and 2 columns.
58247 create table test1b as select * from test3 union all corr
select * from
58248 test1a;
NOTE: Table WORK.TEST1B created, with 0 rows and 2 columns.
58249 create table test2b as select * from test3 union all corr
select * from
58250 test2a;
NOTE: Table WORK.TEST2B created, with 0 rows and 2 columns.
58251 quit;
NOTE: PROCEDURE SQL used:
real time 0.03 seconds
cpu time 0.03 seconds
Sigurd Hermansen wrote:
> Laxxy:
> As usual, SAS offers many ways of restructuring data. This program
> illustrates my favorite way to 'project' columns from tables: data
> test1;
> a=1; b=1; c=1 d=1;
> run;
> data test2;
> x=1; b=1;d=1; e=1;
> run;
> proc sql;
> create table test1a like test1;
> create table test2a like test2;
> create table test3 as select * from test1a union all corr select *
> from test2a;
> create table test1b as select * from test3 union all corr select *
> from test1a;
> create table test2b as select * from test3 union all corr select *
> from test2a; quit;
>
> The first two queries create table shells (metatdata only) from tables
> test1 and test2. The third query lines up corresponding columns of the
> shells (matching on same column name) and projects those columns into
> the shell test3. The last two queries use the test3 shell to project
> the corresponding columns into test1b and test2b.
>
> If you use the OUTER qualifier of the UNION operator, the queries
> would keep the disjoint columns. If you drop the ALL qualifier, the
> queries will eliminate rows in one that duplicate rows in another.
>
> ... no need to worry about the nature of the data, except for one
> critical aspect. Each corresponding column pair have to have the same
> type. Sig
>
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
> laxxy
> Sent: Saturday, July 10, 2004 3:29 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Q: selecting common variables
>
>
> I have two data sets, with around 5000 variables in each. I need to
> narrow each data set down so that they contain all, and only, the
> variables that are common between the two. I do need to merge them,
> they should stay separate.
>
> Is there any way to do this?
>
> Thanks!!!
