topic Re: proc sql join with same variable names in SAS Procedures

proc sql join with same variable names

littlestone — Tue, 12 Apr 2011 20:29:42 GMT

I have following codes:

data daily;
input ID $ YEAR SEASON $ RAINFALL ;
CARDS;
1 1989 SUMMER 20
2 1990 FALL 15
3 1990 SUMMER 25
4 1989 WINTER 11
;
RUN;
data TEMP;
input YEAR SEASON $ TEMP;
CARDS;
1989 WINTER 28
1989 SPRING 52
1989 SUMMER 80
1989 FALL 63
1990 WINTER 33
1990 SPRING 56
1990 SUMMER 78
1990 FALL 65
;
RUN;
PROC SQL;
CREATE TABLE TEST AS
SELECT U1.*,
U2.*
FROM DAILY AS U1 LEFT JOIN TEMP AS U2
ON U1.YEAR=U2.YEAR AND U1.SEASON=U2.SEASON
;
QUIT

There are such warning message in the log:

WARNING: Variable YEAR already exists on file WORK.TEST.
WARNING: Variable SEASON already exists on file WORK.TEST

How can we re-write proc sql to avoid such warning message? I know one way is to explicitly select every single variable like following codes:

PROC SQL;
CREATE TABLE TEST AS
SELECT U1.*,
U2.temp
FROM DAILY AS U1 LEFT JOIN TEMP AS U2
ON U1.YEAR=U2.YEAR AND U1.SEASON=U2.SEASON
;
QUIT

however i am wondering if there is other way, such as using COALESCE function to coalesce those Variables with same names?

Re: proc sql join with same variable names

Ksharp — Wed, 13 Apr 2011 03:46:04 GMT

You need to name these variables which are in both tables manually.
Such as
[pre]
u1.year as u1_year,u2.year as u2_year
...etc

[/pre]

Ksharp Message was edited by: Ksharp

Re: proc sql join with same variable names

sss — Wed, 13 Apr 2011 07:41:32 GMT

Hi Two ways are there to execute the task....
1. by proc sql;
proc sql;
create table season as
select d.*,t.year as yeart, t.season as seasont,temp as tempreture from daily d,temp t
where d.year=t.year and d.season=t.season;
quit;

2. in dataset by merge keyword

proc sort data=daily;
by year season;
run;
proc sort data=temp;
by year season;
run;
data final;
merge temp daily(in=ind);
by year season;
if ind=1;
run;

Re: proc sql join with same variable names

polingjw — Wed, 13 Apr 2011 09:43:31 GMT

Proc sql has an undocumented NOWARN option that will get rid of this warning message.

PROC SQL NOWARN;
CREATE TABLE TEST AS
SELECT U1.*,
U2.*
FROM DAILY AS U1 LEFT JOIN TEMP AS U2
ON U1.YEAR=U2.YEAR AND U1.SEASON=U2.SEASON
;
QUIT;

Re: proc sql join with same variable names

littlestone — Wed, 13 Apr 2011 16:00:36 GMT

Thank you for all for suggestions.

Re: proc sql join with same variable names

annypanny — Mon, 16 Mar 2020 13:36:27 GMT

hi,

The reason this is happening is because when you select *, you are telling sas you want to include everything from both the tables, and as year and season exists on both tables you can't have two variables with the same name. In order to get rid of it, you would need to tell SAS which of the two tables you want to pull year and season from.