Proc SQL name variables

Accepted Solution Solved
Reply
Super Contributor
Posts: 371
Accepted Solution

Proc SQL name variables

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;

 


Accepted Solutions
Solution
‎01-25-2016 11:12 AM
Trusted Advisor
Posts: 1,114

Re: Proc SQL name variables

[ Edited ]

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


All Replies
Respected Advisor
Posts: 3,063

Re: Proc SQL name variables

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.

Grand Advisor
Posts: 17,338

Re: Proc SQL name variables

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;
Grand Advisor
Posts: 9,576

Re: Proc SQL name variables

[ Edited ]

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;

Super Contributor
Posts: 371

Re: Proc SQL name variables

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

Grand Advisor
Posts: 9,576

Re: Proc SQL name variables

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

Esteemed Advisor
Esteemed Advisor
Posts: 7,211

Re: Proc SQL name variables

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

Solution
‎01-25-2016 11:12 AM
Trusted Advisor
Posts: 1,114

Re: Proc SQL name variables

[ Edited ]

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.

☑ This topic is SOLVED.

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

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