topic avoid warning msg in proc sql when merging 2 tables in SAS Procedures

avoid warning msg in proc sql when merging 2 tables

Peter_Y — Tue, 11 Feb 2014 16:28:58 GMT

Hello:

I am trying to merge 2 tables in sql by two variables subject and centre. Here is what I did

proc sql;

create table master_table as

select d1.*, d2.* from d1 and d2 where d1.subject = d2.subject and d1.centre = d2.centre;

quit;

SAS prints a warning message in the log saying that "subject" and "centre" already exist in master_table. I understand the message is due to the common variable names in both datasets. One way to avoid the warning msg is to explicitly spell out all variable names in d1. However, this is really tedious especially when I have hundreds of variables. Is there anyway to say "select all columns other than subject and centre from d1" so the warning is suppressed?

Thanks,

Peter

Re: avoid warning msg in proc sql when merging 2 tables

Amir — Tue, 11 Feb 2014 17:56:16 GMT

Hi,

Before running the code create a macro variable with the column names you want, e.g. (untested):

proc sql noprint;

select name into :d1columns separated by ','

from dictionary.columns

where libname='WORK'

and memname='D1'

and upcase(name) not in ('SUBJECT','CENTRE')

;

quit;

Then you can use the macro variable in your code:

proc sql;

create table master_table as

select &d1columns, d2.* from d1 and d2 where d1.subject = d2.subject and d1.centre = d2.centre;

quit;

Regards,

Amir.

Message was edited by: Amir Malik - fixed memname

Re: avoid warning msg in proc sql when merging 2 tables

Reeza — Tue, 11 Feb 2014 18:07:38 GMT

You can try the feedback option to get all your columns listed, rather than having to type them out.

The select statement will be expanded in the log.

496 proc sql feedback;

497 create table want as

498 select *

499 from sashelp.class as A;

NOTE: Statement transforms to:

select A.Name, A.Sex, A.Age, A.Height, A.Weight

from SASHELP.CLASS A;

NOTE: Table WORK.WANT created, with 19 rows and 5 columns.

500 quit;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.01 seconds

cpu time 0.01 seconds

I also think you should modify your code to read as follows, as this prevents a full Cartesian join. I'd also explicitly specify my join type (inner, left, right, outer)

proc sql;

create table master_table as

select d1.*, d2.*

from d1

join d2

on d1.subject = d2.subject and d1.centre = d2.centre;

quit;

Re: avoid warning msg in proc sql when merging 2 tables

SASKiwi — Tue, 11 Feb 2014 19:00:35 GMT

You can also do a rename (and optionally drop the duplicate columns) to avoid the message:

proc sql;

create table master_table (drop = subject2 centre2) as

select *

from d1, d2 (rename = (subject = subject2 centre = centre2))

where d1.subject = d2.subject2 and d1.centre = d2.centre2;

quit;

Re: avoid warning msg in proc sql when merging 2 tables

Tom — Tue, 11 Feb 2014 19:20:30 GMT

You could try using NATURAL JOIN. It will have issues if there are other common variables, but then you would have received warnings about those with your current code.

proc sql;

create table master_table as

select d1.*, d2.*

from d1 natural join d2

;

quit;