topic Warning "variables already exit" when merging two datasets by proc sql in SAS Programming

Warning "variables already exit" when merging two datasets by proc sql

Phil_NZ — Sat, 18 Jul 2020 20:01:59 GMT

Hi everyone,

When try to merge two dataset that has some distinct Type in common, I face the problem with the result. even try too out at the log file, I also cannot find where the error comes from. I am wondering if you can have a look and give me a suggestion or else.

I have the dataset final (called a).

And I have another raw dataset (called have)

What I want is to match observation in raw data (dataset have) associated with the dataset final based on "Type" and "year" of the dataset final as below (we call dataset want):

By doing this, I use the code as below:

proc sort data=final;
by Type;
run;

proc sort data=have;
by Type;
run;

proc sql;
create Table want as
select a.*,b.*
from final as a, have as b
where (a.Type = b.Type and a.year=b.year);
quit;

And the results as the picture above, but when I check the log, it shows two warnings like that, I am wondering if there is anything wrong in my code or there is anything that I did not concern enough.

 proc sql;
101        create Table merge_ as
102        select a.*,b.*
103        from step4 as a, merge_9_10 as b
104        where (a.Type = b.Type and a.year=b.year);
WARNING: Variable Type already exists on file WORK.MERGE_.
WARNING: Variable year already exists on file WORK.MERGE_.

Thanks in advance!

Re: Warning "variables already exit" when merging two datasets by proc sql

PaigeMiller — Sat, 18 Jul 2020 20:06:25 GMT

I believe the WARNING in the log is because you used

select a.*,b.*

and the WARNING correctly tells you that both TYPE and YEAR exist in both data sets.

The WARNING would go away if you used

select a.*,b.isin,b.secd,b.geogn,...

(you can type the rest of the variable names except year)

Re: Warning "variables already exit" when merging two datasets by proc sql

Phil_NZ — Sat, 18 Jul 2020 20:24:53 GMT

Thanks @Paige Miller, it works for me now!

Re: Warning "variables already exit" when merging two datasets by proc sql

hellohere — Wed, 19 Jan 2022 03:54:47 GMT

any way to remove this warning?!