topic Re: can I make my query dynamically ignore join codes? in SAS Programming

can I make my query dynamically ignore join codes?

mrdlau — Mon, 14 May 2018 14:03:26 GMT

I have to upload various tables(xlsx files) and join them together via a query. I,e upload table A1, and join it with A2, A3.....etc.

Can I make it such that my query is dynamic enough to ignore join statements if my table does not have certain columns.

For example, uploading table A1, A2, A3, I might join them with primary Key "ID" and 'name'. However, if I upload table B1, B2, B3, it might not have Primary Key "name."

So I want to have a query that has all the join staements for name and ID, but be able to ignore the 'name' join if these columns aren't available.

Re: can I make my query dynamically ignore join codes?

RW9 — Mon, 14 May 2018 14:14:24 GMT

Whilst there might be, after some major code work, a way of doing something similar to what you want the real question is why you don't know your data. Programming is 95% data and documentation, with a small amount of coding at the end. Writing code based off data you don't know and trying to generalise things will just cause you far more work. Know the data, know the linking variables - this is the data model and fundamental to any programming endeavor.

Re: can I make my query dynamically ignore join codes?

tomrvincent — Mon, 14 May 2018 14:23:47 GMT

Sure. Use dictionary.columns to check to see if the field(s) exist and then use macro code (%if) to bypass joins that wouldn't work (make the whole thing a macro to use things like %if).

something like:

PROC SQL noprint ;
SELECT *
FROM DICTIONARY.COLUMNS
WHERE LIBNAME='5XX' and memname='B1' and name='name' ;
QUIT;
%if &sqlobs>0 %then... (do your join by name)

Re: can I make my query dynamically ignore join codes?

ProcWes — Mon, 14 May 2018 15:20:56 GMT

Yeah dictionary or a proc contents out=. Maybe with a select into:.

Re: can I make my query dynamically ignore join codes?

tomrvincent — Mon, 14 May 2018 15:23:28 GMT

I considered that, but I didn't see anything that needed to be saved...just the existence of the field itself is all that's needed.

Re: can I make my query dynamically ignore join codes?

Reeza — Mon, 14 May 2018 15:40:57 GMT

@mrdlau wrote:

So I want to have a query that has all the join staements for name and ID, but be able to ignore the 'name' join if these columns aren't available.

Would those tables still be joined with ID or ignored entirely?

Re: can I make my query dynamically ignore join codes?

mrdlau — Mon, 14 May 2018 17:39:25 GMT

These tables would still need to be joined by ID, so I would just want the ‘name’ join to be ignored, since that table doesn’t have a name column.

Re: can I make my query dynamically ignore join codes?

mrdlau — Tue, 15 May 2018 04:33:47 GMT

Thank you. Im' still new to SAS and macros, and still need help understanding. Would appreciate some assistance if possible.

Here's an example full code that I have.

proc sql;
select *
from 
work.table1 t1
left join work.table2 t2 on t1.ID = t2.ID and t1.name = t2.name;
quit;

In my tables, t2 does not have a 'name' column, so running the query like this, it'll error out. I want my query to be able to ignore the name joins so it'll run as if there wasn't a name join

I understand the first part of you code where it's filtering the dictionary columns to the 'Name' column. However, I dont understand the rest of it.

proc sql;
select * from dictionary.columns
where LIBNAME = 'WORK'
AND memname = 'TABLE2'
AND name = 'Name';
quit;

is &sqlobs a predefined macro? and is this already referencing the above script? I'm actually still very new to sas and macros, so I'm still unsure how to apply what you gave me to my code above. my extent to macros at the moment is %let = variable, and then referencing it throughout the code

Re: can I make my query dynamically ignore join codes?

RW9 — Tue, 15 May 2018 07:57:32 GMT

Here is an example:

data a1;
  id=1; var1=123; output;
  id=2; var1=345; output;
run;
data a2;
  id=1; name="abc"; var2=12; output;
  id=3; name="def"; var2=90; output;
run;
data a3;
  id=3; name="tmp"; var3=20; output;
  id=4; name="rtt"; var3=34; output;
run;

%macro Join_ID (t1=,t2=,use_name=N,tresult=);
  proc sql;
    create table &tresult. as
    select *
    from   &t1. t1
    left join &t2. t2
    on     t1.id=t2.id
    %if &use_name.=Y %then %do;
      and t1.name=t2.name
    %end;
    ;
  quit;
%mend Join_ID;

%Join_ID (t1=a1,t2=a2,tresult=first);
%Join_ID (t1=first,t2=a3,use_name=Y,tresult=second);

You can switch the call of Join_ID based on what is in sashelp.vcolumn.

However, once again, I strongly recommend you don't go down that route as you will have problems, not just logical ones, but inaccurate joins and such like. Know your data, program to your data, this is the way to write robust clean code which can repeatedly produce the same accurate result.

Re: can I make my query dynamically ignore join codes?

Tom — Tue, 15 May 2018 12:32:12 GMT

If the key variables are the only variables they have in common the consider using NATURAL joins. SAS will automatically join based on variables with the same names.

proc sql ;
create table want as
select *
from b2 
natural join b1
;
quit;