- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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