SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

merging multiple variables with same name

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

merging multiple variables with same name

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,


Accepted Solutions
Solution
‎02-15-2017 05:28 PM
PROC Star
Posts: 1,100

Re: merging multiple variables with same name

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;

 

View solution in original post


All Replies
Super User
Posts: 17,948

Re: merging multiple variables with same name

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.

 

Contributor
Posts: 50

Re: merging multiple variables with same name

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.
Super User
Posts: 17,948

Re: merging multiple variables with same name

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. 

Contributor
Posts: 50

Re: merging multiple variables with same name

Thank you
Super User
Posts: 9,691

Re: merging multiple variables with same name

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;


Solution
‎02-15-2017 05:28 PM
PROC Star
Posts: 1,100

Re: merging multiple variables with same name

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;

 

Contributor
Posts: 50

Re: merging multiple variables with same name

Thank you
SAS Employee
Posts: 10

Re: merging multiple variables with same name

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 647 views
  • 6 likes
  • 5 in conversation