Good morning to all and many thnks for your kindly help !!!
I need to do a Proc Sql with two DB that has the same named columns and I need to keep all them, there is a way to rename automatically the second columns DB?
Many tnks
Tecla
AFAIK, no.
If there is a considerable number of columns, you can create a macro variable containing the code for all columns that you want to do this for:
data class1;
set sashelp.class;
run;
data class2;
set sashelp.class;
run;
data _null_;
set sashelp.vcolumn end=done;
where libname = "WORK" and memname = "CLASS2" and upcase(name) ne "NAME";
/* the last condition excludes the key variable on which the join will be made */
length statement $1000;
retain statement;
statement = catx(",",statement,"b."!!name!!" as b_"!!name);
if done then call symputx("statement",statement);
run;
proc sql;
create table want as
select
a.*,
&statement.
from class1 a, class2 b
where a.name = b.name
;
quit;
AFAIK, no.
If there is a considerable number of columns, you can create a macro variable containing the code for all columns that you want to do this for:
data class1;
set sashelp.class;
run;
data class2;
set sashelp.class;
run;
data _null_;
set sashelp.vcolumn end=done;
where libname = "WORK" and memname = "CLASS2" and upcase(name) ne "NAME";
/* the last condition excludes the key variable on which the join will be made */
length statement $1000;
retain statement;
statement = catx(",",statement,"b."!!name!!" as b_"!!name);
if done then call symputx("statement",statement);
run;
proc sql;
create table want as
select
a.*,
&statement.
from class1 a, class2 b
where a.name = b.name
;
quit;
PS if your purpose is to compare the values in the two tables, consider using PROC COMPARE.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.