2

I'm trying to do a join on a SAS table using PROC-SQL. Depending on the value of one variable in table A I need to join using a column, otherwise I need to use another column.

proc sql;
create table test
as select
A.*,
A.PPNG_RVNE * B.perc_ress as variable1,
A.P_RVNE    * B.perc_ress as variable2
from      tableA    as A
left join tableB    as B
on case when A.cod_cia = 1 then A.cod_cia=B.cod_cia and A.cod_agrup_produto=B.cod_agrup_produto 
                           else A.cod_cia=B.cod_cia and A.projeto=B.projeto;

I need to join just to create variable1 and variable2. I don't want to select any variable from table B.

My cod doesn't run. SAS gives me an error message saying that it is expecting an `end.

Does anyone know how to conditional join depending on columns?

Rods2292
  • 665
  • 2
  • 10
  • 28

3 Answers3

1

Don't use case. Just express the logic booleanly"

proc sql;
create table test as
    select A.*, A.PPNG_RVNE * B.perc_ress as variable1, A.P_RVNE    * B.perc_ress as variable2
    from tableA A left join
         tableB B
         on A.cod_cia = B.cod_cia and
            (A.cod_cia = 1 and A.cod_agrup_produto = B.cod_agrup_produto) or
            (A.cod_cia <> 1 and A.projeto = B.projeto);

Note: This uses <> 1. If cod_cia could be NULL you need to take that into account. Also note that this factors out the first condition.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Not a PROC-SQL user so apologies if this is wrong, but based on this question a case statement needs the format:

CASE
    WHEN ... THEN ...
    WHEN ... THEN ...
    ELSE ...
END

So have you tried:

proc sql;
create table test
as select
A.*,
A.PPNG_RVNE * B.perc_ress as variable1,
A.P_RVNE    * B.perc_ress as variable2
from      tableA    as A
left join tableB    as B
on case when A.cod_cia = 1 then A.cod_cia=B.cod_cia and A.cod_agrup_produto=B.cod_agrup_produto 
        else A.cod_cia=B.cod_cia and A.projeto=B.projeto
   end;
Luke Merrett
  • 5,724
  • 8
  • 38
  • 70
0

I would use two joins and a coalesce statement:

proc sql;
create table test
as select
A.*,
A.PPNG_RVNE * coalesce(B1.perc_ress,B2.perc_ress) as variable1,
A.P_RVNE    * coalesce(B1.perc_ress,B2.perc_ress) as variable2
from      tableA    as A
left join tableB    as B1
    on A.cod_cia = 1 and A.cod_cia=B1.cod_cia and A.cod_agrup_produto=B1.cod_agrup_produto
left join tableB    as B2
    on  A.cod_cia=B2.cod_cia and A.projeto=B2.projeto;

you could add A.cod_cia ne 1 in the second join, but it's not necessary unless.

ALong
  • 1
  • 2