topic Re: PROC SQL DB WITH SAME COLUMNS in SAS Programming

PROC SQL DB WITH SAME COLUMNS

Tecla1 — Thu, 01 Apr 2021 07:40:45 GMT

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

Re: PROC SQL DB WITH SAME COLUMNS

Kurt_Bremser — Thu, 01 Apr 2021 08:03:33 GMT

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;

Re: PROC SQL DB WITH SAME COLUMNS

Kurt_Bremser — Thu, 01 Apr 2021 08:05:00 GMT

PS if your purpose is to compare the values in the two tables, consider using PROC COMPARE.

Re: PROC SQL DB WITH SAME COLUMNS

Tecla1 — Tue, 06 Apr 2021 07:17:54 GMT

Tnks for your kindly replay.

Tecla