CODEWITHSUNDEEP
sqlsql-serversas
Metariat
Metariat

Reputation: 532

Conditional merge SAS PROC SQL

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?

Upvotes: 0

Views: 1090

Answers (1)

Joe
Joe

Reputation: 63424

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

Upvotes: 2

Related Questions