merging multiple variables with same name

[r4321]

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,

R 

[Reeza]

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. 

Ie 

rename =( var1-var10 = new_var1-new_var10)

SQL doesn't allow variable shortcuts or variable lists.

[r4321]

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.

[Reeza]

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. 

[r4321]

Thank you

[Ksharp]

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;

[TomKari · Accepted solution]

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;

[r4321]

Thank you

[SteveED]

R,

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