topic Re: Proc SQL name variables in SAS Programming

Proc SQL name variables

hhchenfx — Sun, 24 Jan 2016 22:47:11 GMT

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,

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;

Re: Proc SQL name variables

SASKiwi — Sun, 24 Jan 2016 23:12:24 GMT

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.

Re: Proc SQL name variables

Reeza — Mon, 25 Jan 2016 00:35:17 GMT

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;

Re: Proc SQL name variables

Ksharp — Mon, 25 Jan 2016 01:12:58 GMT

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;

Re: Proc SQL name variables

hhchenfx — Mon, 25 Jan 2016 01:33:35 GMT

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

Re: Proc SQL name variables

Ksharp — Mon, 25 Jan 2016 01:42:18 GMT

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

Re: Proc SQL name variables

FreelanceReinh — Mon, 25 Jan 2016 10:55:10 GMT

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.

Re: Proc SQL name variables

RW9 — Mon, 25 Jan 2016 11:04:22 GMT

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