translate sas data step to proc sql

[Augusto]

Hi Guys,

I need to translate this merge data step code to proc sql, is there anyone to help me with it, please?

Thanks.

for table have1 I have this structure:

CLIENTE

COD_GRUPO_ATUAL

COD_SEG_ATUAL

CPFCNPJ

DIAS_ATRASO_1

GRUPO_ATUAL 2

RISCO_MOROSO_ATUAL

for table have2 I have this structure

CLIENTE

COD_GRUPO_ATUAL

COD_SEG_ATUAL

CPFCNPJ

DIAS_ATRASO_0

GRUPO_ATUAL 2

RISCO_TOT_ATUAL

data want (compress = yes 

keep = CPFCNPJ CLIENTE COD_GRUPO_ATUAL GRUPO_ATUAL

COD_SEG_ATUAL RISCO_TOTAL_ATUAL DIAS_ATRASO_ATUAL RISCO_MOROSO_ATUAL);

merge have1 (in = a)
      have2     (in = b);

   by CPFCNPJ;

   if a and b then
   do;
      DIAS_ATRASO_ATUAL = DIAS_ATRASO_1;
      RISCO_TOTAL_ATUAL = RISCO_TOT_ATUAL + RISCO_MOROSO_ATUAL;
   end;
   else
   do;
      if a and not b then
      do;
         DIAS_ATRASO_ATUAL = DIAS_ATRASO_0;
         RISCO_TOTAL_ATUAL = RISCO_TOT_ATUAL;
      end;
      else
      do;
         if not a and b then
         do;
            DIAS_ATRASO_ATUAL = DIAS_ATRASO_1;
            RISCO_TOTAL_ATUAL = RISCO_MOROSO_ATUAL;
         end;
      end;
   end;
run;

[Haikuo]

Well, there are something in your code I don't quite undertand.

if a and not b then

      do;

         DIAS_ATRASO_ATUAL = DIAS_ATRASO_0;

         RISCO_TOTAL_ATUAL = RISCO_TOT_ATUAL;

      end;

DIAS_ATRASO_0 and RISCO_TOT_ATUAL only exists in 'have2(in= b)', when you 'if a and not b', DIAS_ATRASO_0 and RISCO_TOT_ATUAL will always be missing. So is this a typo? Same thing happens to the code downstairs as well.

Any way, what you were asking is quite doable using a combination of joins and unions in SQL. I have attached a sample code using sashelp.class to show the basic ideas, and hopefully can help you tackle your real life data.

data first15 last15;

set sashelp.class nobs=nobs;

if _n_<=15 then output first15;

if _n_>=nobs-14 then output last15;

run;

proc sql;

create table want as

select distinct * from

(select a.*, (a.age+b.age) as n_age from first15 a, last15 b where a.name=b.name)

union

(select a.*, (a.age*10) as n_age from first15 a where a.name not in

(select name from last15))

union

(select b.*, (b.age/10) as n_age from last15 b  where b.name not in

   (select name from first15))

   ;quit;

Regards,

Haikuo

[PGStats]

Something like such :

proc sql;

create table want as

select

coalesce(a.CPFCNPJ, b.CPFCNPJ) as CPFCNPJ,

coalesce(a.CLIENTE, b.CLIENTE) as CLIENTE,

coalesce(a.COD_GRUPO_ATUAL, b.COD_GRUPO_ATUAL) as COD_GRUPO_ATUAL,

coalesce(a.COD_SEG_ATUAL, b.COD_SEG_ATUAL) as COD_SEG_ATUAL,

coalesce(RISCO_TOT_ATUAL, 0) + coalesce(RISCO_MOROSO_ATUAL, 0) as RISCO_TOTAL_ATUAL,

coalesce(DIAS_ATRASO_1, DIAS_ATRASO_0) as DIAS_ATRASO_ATUAL,

RISCO_MOROSO_ATUAL

from have1 as a full join have2 as b on a.CPFCNPJ=b.CPFCNPJ;

should do the job.

PG

[PGStats · Accepted solution]

Or simpler yet, a rare opportunity to use a natural full join :

proc sql;

create table want as

select CPFCNPJ, CLIENTE, COD_GRUPO_ATUAL, COD_SEG_ATUAL,

     sum(RISCO_TOT_ATUAL, RISCO_MOROSO_ATUAL) as RISCO_TOTAL_ATUAL,

     coalesce(DIAS_ATRASO_1, DIAS_ATRASO_0) as DIAS_ATRASO_ATUAL,

     RISCO_MOROSO_ATUAL

from have1 natural full join have2;

PG

[Augusto]

tks PGStats