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;
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
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
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
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
tks PGStats
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.