topic Re: merging multiple variables with same name in SAS Data Management

merging multiple variables with same name

r4321 — Tue, 14 Feb 2017 23:19:27 GMT

Hello all, I'm trying to merge multiple variables with the same name. Instead of writing it out, I would like to find a shortcut if possible. Here is my current code:

proc sql;
create table all.as6 as select a.*, b.var5_101, b.var5_102, b.var5_103, b.var5_104, b.var5_105, b.var5_106, b.var5_107, b.var5_108, b.var5_109, b.var5_110, b.var5_111, b.var5_112, b.var5_113, b.var5_114, b.var5_115, b.var5_116, b.var5_117, b.var5_118, b.var5_119, b.var5_120, b.var5_121, b.var5_122, b.var5_123, b.var5_124, b.var5_125, b.var5_126, b.var5_127, b.var5_128, b.var5_129, b.var5_130
from all.as5 as a left join all.asa1 as b
on a.key = b.key;
quit;

I would like to just do something like:

proc sql;
create table all.as6 as select a.*, b.var5_101-var5_130
from all.as5 as a left join all.asa1 as b
on a.key = b.key;
quit;

Unfortunately, this code does not work. What can I do?

Thanks,

Re: merging multiple variables with same name

Reeza — Tue, 14 Feb 2017 23:28:03 GMT

Use a data step and you can rename enmasse. I don't see any renames in your code so not sure how you're currently doing it.

rename =( var1-var10 = new_var1-new_var10)

SQL doesn't allow variable shortcuts or variable lists.

Re: merging multiple variables with same name

r4321 — Wed, 15 Feb 2017 00:24:29 GMT

Thanks - I know about the renaming, but is there a way I can merge the same way? There's gotta be a quicker way than typing out each variable name one by one.

Re: merging multiple variables with same name

Reeza — Wed, 15 Feb 2017 01:58:05 GMT

Not in SQL. There are no variable lists or shortcuts in SQL.

Though you're not using any renaming, it's only mentioned in the title so where does the same name issue come into play?

Why not just use a * as well?

You can use FEEDBACK to select and then grab the code from the log and modify.

proc sql FEEDBACK;

create table want as

select *

from sashelp.class;

quit;

Or you can do the join in a data step, but you'll still need to deal with the variables with the same names.

Re: merging multiple variables with same name

Ksharp — Wed, 15 Feb 2017 02:07:03 GMT

Make a macro variable to hold these variable names.

data x;
 do i=101 to 130;
  output;
 end;
run;
proc sql;
select cats('b.var5_',i) into : vars  separated by ','
 from x ;

create table all as 
 select a.*, &vars
  ................
quit;

Re: merging multiple variables with same name

TomKari — Wed, 15 Feb 2017 02:40:44 GMT

Another option:

Tom

%macro DoSQL;

proc sql;

create table work.as6 as select a.*,

%Do i = 101 %to 129;

b.var5_&i.,

%end;

b.var5_130

from work.as5 as a left join work.asa1 as b

on a.key = b.key;

quit;

%mend;

%DoSQL;

Re: merging multiple variables with same name

SteveED — Wed, 15 Feb 2017 14:05:04 GMT

another quick option would be to use the power of data step and a view.

-------------------------------------------------------------

data work._t_asa1/view=work._t_asa1;

set all.asa1(keep=key var5_101-var130);

run;

proc sql;

create table all.as6 as

select a.*,b.*

from all.as5 as a left join work._t_asa1 as b

on a.key=b.key;

quit;

proc datasets lib=work noprint; /* clean up */

delete _t_asa1/memtype=view;

run;

--------------------------------------------------------

You'll get a dup key warning on the field "key" but it can be ignored.

Outside of that Tom's macro loop is a good option.

Steve

Re: merging multiple variables with same name

r4321 — Wed, 15 Feb 2017 22:27:27 GMT

Thank you

Re: merging multiple variables with same name

r4321 — Wed, 15 Feb 2017 22:27:41 GMT

Thank you