Conditional merge SAS PROC SQL

Question

In my original code, I want to merge several data bases:

%let theft=1;

proc sql;
create table NewTable as
Select *

From 
   dtm_sitecom.FactSitecAllCriteria SITEC

 left join dtm_sitecom.FactClaimLastVersion FactClaimLastVersion
    on FactClaimLastVersion.ClaimId = Sitec.ClaimId

/*Condition 1 here*/
/*If &Theft=1 then*/
 left join dtm_sitecom.DimClaimState DimClaimState
    on DimClaimState.ClaimStateKey = FactClaimLastVersion.ClaimStateKey

/*Condition 2 here*/
/*else if &Theft = 0 then*/
 left JOIN dtm_sitecom.DimGeoRisk dgr 
    ON dgr.GeoRiskKey = SITEC.GeoRiskKey;
quit;

On the upgraded version, I want to add some conditions (/Condition 1/ and /Condition 2/ in the code) such as, if the value of the variable Theft=1 then do the merge with the talbe dtm_sitecom.DimClaimState, otherwise do the merge with the other table. I tried the code as if there is no comment sign around the if, but it didn't work because of the wrong syntax. Is there any SAS syntax allowing me to do that?

Give us an example of what you're trying to do. The on clause is a condition, so what kind of condition are you trying to add that's not going to work there? — Joe
If theft=0 could you have any successful matches in DimClaimState? Or are those only going to match when theft=1? — Joe
@Joe: Theft is just a variable assigned by %let theft=1;. So that in the begining of the program I can control which data to be merged, i.e I could have any sucessful matches. — Metariat

Joe Joe · Accepted Answer · 2017-01-10T15:49:03

If you are joining on a variable in the data, you can include that in the on statement:

data class_attach_m;
  set sashelp.class;
  attach_var='M';
run;

data class_attach_f;
  set sashelp.class;
  attach_var='F';
run;

proc sql;
  create table class as
    select class.*, coalescec(class_attach_m.attach_var,class_attach_f.attach_var) as attach_var from sashelp.class
    left join class_attach_m 
    on class.name = class_attach_m.name
      and class.sex='M'
    left join class_attach_f
    on class.name = class_attach_f.name
      and class.sex='F'
  ;
quit;

coalescec (or coalesce for numerics) combines the two brought-in fields.

If you are using macro variables to control which you run, though, you need to be running this in a macro; if you're in a macro context, you can use %if to control what is executed.

For example, using the previous example - and I use a macro parameter not a %let as that's the right way to do it, though you can define that parameter using %let earlier and use it for the parameter call:

%macro attach_which(attach_m=M);

proc sql;
  create table class as
    select class.*, attach_var from sashelp.class
    %if &attach_m=M %then %do;
      left join class_attach_m 
      on class.name = class_attach_m.name
    %end;
    %else %do;
      left join class_attach_f
      on class.name = class_attach_f.name
    %end;
  ;
quit;
%mend attach_which;

%attach_which(attach_m=M);

Conditional merge SAS PROC SQL

1 Answers