Hi Everyone,
I have a simple question. When I merge data A and data B and want to take a number of variables in B.
Yes, I will list all variables needed but Is there any way I dont have to repeated by type "B." B.var1, B.var2...
Thank you,
HC
proc sql;
create table sys1
as select a.*, var1, var2 var3 var4
from sys as a left join Ret_Buy_fix_TP_notrail_H4 as b
on a.key=b.key;quit;
You could write a short utility macro as shown below and put it into your SASAUTOS library, so that it is automatically available in the future.
%macro b(list);
b.%sysfunc(tranwrd(%cmpres(&list),%str( ),%str(,b.)))
%mend b;
Now you can write your select statement like
select a.*, %b(var1 var2 var3 var4)
and the macro expression will resolve to b.var1,b.var2,b.var3,b.var4.
With variations of this macro you could be able to specify the alias (b in the above example) as a macro parameter or to deal with variable lists in order to reduce tedious typing to a minimum.
Edit: Added %cmpres so as to make macro B robust against inadvertent double spaces in the list of variable names.
What you are doing will only work if all of the variables you choose are not in dataset A also. If any are then you have to specify from which dataset you want to read them.
A bit of a trick - use the FEEDBACK option on your proc SQL statement.
Then grab the code from the log and modify it as necessary.
proc sql feedback;
select a.*, b.*
from table1 as A
left join as table2 as B
on a.id=b.id;
quit;
Maybe you could try NATURE left join:
Assuming there is only one variable ID in common in both datasets.
proc sql;
create table sys1
as select *
from sys as a nature left join Ret_Buy_fix_TP_notrail_H4 as b ;
quit;
The issue is there are like 100 variable and I only want to take say 60 variables from that file B to the new file and I dont want to keep typing b. b. b. ....
Then use the following code and pick up the variable name you need into a macro variable via SQL.
proc transpose data=have(obs=0) out=temp;
var _all_;
run;
proc sql;
............. into : list separated by ','
Why do you have a dataset with 100 variables? I tend to advise on using normalised data structures (long rather than wide) as it makes programming easier, and SQL is specifically designed to work with normalised and relational datasets. If this is due to a transpose - most likely - then don't use SQL as your code will not be easy. Use datastep as datastep is designed to work with tables and provides functionality to work with wide tables - called arrays. But it depends on the data which you haven't shown an example of (in the form of a datastep).
You could write a short utility macro as shown below and put it into your SASAUTOS library, so that it is automatically available in the future.
%macro b(list);
b.%sysfunc(tranwrd(%cmpres(&list),%str( ),%str(,b.)))
%mend b;
Now you can write your select statement like
select a.*, %b(var1 var2 var3 var4)
and the macro expression will resolve to b.var1,b.var2,b.var3,b.var4.
With variations of this macro you could be able to specify the alias (b in the above example) as a macro parameter or to deal with variable lists in order to reduce tedious typing to a minimum.
Edit: Added %cmpres so as to make macro B robust against inadvertent double spaces in the list of variable names.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.