Help using Base SAS procedures

proc sql join with same variable names

Reply
Frequent Contributor
Posts: 89

proc sql join with same variable names

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?
Super User
Posts: 9,682

Re: proc sql join with same variable names

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
Contributor sss
Contributor
Posts: 65

Re: proc sql join with same variable names

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;
Regular Contributor
Posts: 171

Re: proc sql join with same variable names

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;
Frequent Contributor
Posts: 89

Re: proc sql join with same variable names

Thank you for all for suggestions.
Ask a Question
Discussion stats
  • 4 replies
  • 3942 views
  • 0 likes
  • 4 in conversation