topic Re: Proc Sql - same columns in SAS Programming

Proc Sql - same columns

Tecla1 — Tue, 24 Mar 2020 18:02:28 GMT

Good Afternoon,

I need to use a proc sql with two DB that has the same columns (name), so I think I have to rename the columns of one of the two tables (I need to Keep all the columns of the two DB), but I have a big number of columns and it's not so easy to list all. I find some soluction with "Index" function but I'm not able to use it. Please can you help me ?

Tnks a lot.

Tecla

Re: Proc Sql - same columns

Reeza — Tue, 24 Mar 2020 18:11:35 GMT

If you have the same column names what are you trying to do overall?

Sometimes it makes more sense to append and transpose?

You can add a prefix/suffix to all the variables as well, which is another option. If your table name was SAMPLE in the WORK library this would add the NEW_ prefix to each variable name. Note that it is case sensitive for the comparisons.

Let prefix = new_;

proc sql noprint;
select catx("=", name, catx("_", "&prefix.", name))
into :rename_list
separated by " "
from sashelp.vcolumn
where libname='WORK'
and memname='SAMPLE';
quit;


%put &rename_list;


proc datasets library=work nodetails nolist;
modify sample;
rename &rename_list;
run; quit;

proc print data=sample noobs;
run;

Re: Proc Sql - same columns

ballardw — Tue, 24 Mar 2020 19:14:05 GMT

It will help to provide a clear example of what your are attempting to do. As in show a sample of the two data sets, maybe 5 variables each where you have the "same columns" and what the result is expected to be when things are done.

And perhaps SQL isn't what you need. The data step allows uses of variable name lists that may make things easier. But without seeing a pretty concrete example of what you need it is hard to tell.

Best is if the data could be provided in the form of a data step so we have something to manipulate. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.