BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
r4321
Pyrite | Level 9

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,

1 ACCEPTED SOLUTION

Accepted Solutions
TomKari
Onyx | Level 15

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

8 REPLIES 8
Reeza
Super User

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
Pyrite | Level 9
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
Super User

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
Pyrite | Level 9
Thank you
Ksharp
Super User
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
Onyx | Level 15

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
Pyrite | Level 9
Thank you
SteveED
SAS Employee

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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