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

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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.

View solution in original post

7 REPLIES 7
SASKiwi
PROC Star

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.

Reeza
Super User

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;
Ksharp
Super User

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;

hhchenfx
Barite | Level 11

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. ....

Ksharp
Super User

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 ','

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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). 

FreelanceReinh
Jade | Level 19

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.

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 Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1886 views
  • 3 likes
  • 6 in conversation