Hello I'm new in programming with SAS and i need a help to convert a T-SQL Stored Procedure to SAS. One of my doubts it's in this following lines of t-sql: --Create a temp table with the necessaries data types PROC SQL
insert into #Contratos
select
Convert(date,NULL) dt_comp,
Convert(integer,NULL) nr_ctr, Convert(tinyint,NULL) nr_adl,
Convert(tinyint,NULL) cd_emp, Convert(smallint,NULL) cd_und,
Convert(varchar(10),NULL) sg_mod, Convert(integer,NULL) cd_cli,
Convert(varchar(10),NULL) idContrato_Credito, Convert(varchar(30),NULL) produtoCredito_Credito,
Convert(integer,NULL) idContaCorrente, Convert(smallint,NULL) idAgencia,
Convert(smallint,NULL) idPosto, Convert(integer,NULL) idCliente,
Convert(varchar(14),NULL) cpf_cnpj_Credito, Convert(varchar(20),NULL) dataEmissao_Credito,
Convert(decimal(18,2),NULL) valorTotal_Credito, Convert(decimal(18,2),NULL) saldoDevedor_Credito,
Convert(decimal(18,2),NULL) saldoContabil_Credito, Convert(integer,NULL) parcelasTotais_Credito,
Convert(integer,NULL) parcelasAtrasadas_Credito, convert(date, null) dt_ope,
convert(integer,null) qt_dd_atr_dc, Convert(date,null) dataCBP,
CONVERT(integer,null) cd_und_cc, CONVERT(varchar(14),null) cd_usu_atu,
CONVERT(integer,null) parcelasPagas, CONVERT(varchar(20),null) status,
CONVERT(decimal(18,2),NULL) valorImovel, CONVERT(varchar(20),null) matCadOperacao,
CONVERT(DATE,null) dataCadOperacao, CONVERT(varchar(20),null) faseOperacao,
Convert(decimal(18,2),NULL) valorParcela, convert(date, null) dataVencParcela,
Convert(decimal(18,2),NULL) excessoLimite_subSelect,CONVERT(varchar(20),null) sistAmort,
Convert(varchar(10),NULL) sg_gp_ctb, Convert(decimal(18,2),NULL) valorInscritoCBP
into #Contratos where 1 = 2
insert into #Contratos
select
G.dt_gp_ctb,
G.nr_ctr,
G.nr_adl,
G.cd_emp,
G.cd_und,
G.sg_mod,
G.cd_cli,
Convert(varchar,G.nr_ctr)+dbo.f_number(G.nr_adl,3),
RTrim(M.ds_mod),
C.cd_cc,
case when (U.cd_ag_sup is null) then C.cd_und else C.cd_und/100 end,
case when (U.cd_ag_sup is not null) then right(C.cd_und,2) else '00' end,
C.cd_cli,
P.nr_cic,
C.dt_ope,
C.vr_pcp,
0.00,
G.sd_atl_ctb_d0,
C.qt_tit,
0,
c.dt_ope,
(select case when datediff(d,min(tiab.dt_ven),@dt_fim) >0 then datediff(d,min(tiab.dt_ven),@dt_fim) else 0 end diasatraso from linked_clust02.TB_CREDIMASTER.dbo.t402tiab tiab where tiab.cd_cli = c.cd_cli and tiab.nr_ctr = C.nr_ctr and tiab.nr_adl = C.n
r_adl and tiab.sg_mod = C.sg_mod) qt_dd_atr_dc,
(select MIN(DT_INC_GP) from linked_clust02.TB_CREDIMASTER.dbo.t402cogc where cd_cli = c.cd_cli and nr_ctr = C.nr_ctr and nr_adl = C.nr_adl and sg_mod = C.sg_mod and substring(sg_gp_ctb,1,3) = 'CBP') dataCBP,
case m.sg_lin_ger when 'EMP' THEN C.cd_und ELSE 0 END cd_und,
C.cd_usu_atu,
(select count(*) from linked_clust02.TB_CREDIMASTER.dbo.t402titu t
where c.cd_emp=t.cd_emp and c.cd_und=t.cd_und
and c.cd_cli=t.cd_cli and c.nr_ctr=t.nr_ctr
and c.nr_adl=t.nr_adl and c.sg_mod=t.sg_mod and t.dt_lqd is not null) parcPagas,
(CASE
WHEN C.id_sit_ctr='AT' or C.id_sit_ctr='RN' THEN
'ATIVO'
WHEN C.id_sit_ctr='EX' or C.id_sit_ctr='IN'THEN
'CANCELADA'
END) situacao
,ga.vr_gar valorImovel
,'NAO SE APLICA' matCadOperacao
,C.dt_ope dataCadOperacao
,'NAO SE APLICA' faseOperacao
,(select top 1 t.vr_tit from linked_clust02.TB_CREDIMASTER.dbo.t402titu t
where c.cd_emp=t.cd_emp and c.cd_und=t.cd_und
and c.cd_cli=t.cd_cli and c.nr_ctr=t.nr_ctr
and c.nr_adl=t.nr_adl and c.sg_mod=t.sg_mod
) valorParcela
,(select t.dt_ven from linked_clust02.TB_CREDIMASTER.dbo.t402titu t
where c.cd_emp=t.cd_emp and c.cd_und=t.cd_und
and c.cd_cli=t.cd_cli and c.nr_ctr=t.nr_ctr
and c.nr_adl=t.nr_adl and c.sg_mod=t.sg_mod
and c.qt_tit=1) dataVencParcela
,(SELECT top 1 vw.sd_tmd_exs FROM vw_t401rsdc vw With (NOLOCK)
where vw.cd_und=C.cd_und and vw.cd_cc=C.cd_cc and vw.dt_mov between @dt_ini and @dt_fim order by vw.dt_mov desc) excessoLimite_subSelect
,'NAO SE APLICA' sistAmort,
C.sg_gp_ctb,
null
from linked_clust02.TB_CREDIMASTER.dbo.t402cogc G With (NOLOCK)
join linked_clust02.TB_CREDIMASTER.dbo.t402cont C With (NOLOCK)
on (G.cd_emp = C.cd_emp
and G.cd_und = C.cd_und
and G.cd_cli = C.cd_cli
and G.nr_ctr = C.nr_ctr
and G.nr_adl = C.nr_adl
and G.sg_mod = C.sg_mod)
join linked_clust02.TB_CREDIMASTER.dbo.t402moda M With (NOLOCK) on (C.sg_mod = M.sg_mod)
join linked_clust02.tb_basico.dbo.t400pess P With (NOLOCK) on (C.cd_cli = P.cd_cli)
join linked_clust02.tb_basico.dbo.t400unop U With (NOLOCK) on (U.cd_emp = C.cd_emp and C.cd_und = U.cd_und_fis)
left join tfs_garantias..t472gara ga With (NOLOCK)
on (ga.cd_cli = C.cd_cli and ga.ds_gar='ALIMO')
where M.sg_mod in ('CAPGIRO','CAPGIROF','GIRORAP','GIRORAPF','RENEGPJ','RENEGPJF',
'CHQCBPPF','CHQCBPPJ','EXSCBPPF','EXSCBPPF','ADCBPPJ','ADCBPPF')
and G.sg_gp_ctb <> 'CONTRATO' and G.sd_atl_ctb_d0 > 0
and G.dt_gp_ctb = @dt_fim
and (isnull(@ag,0) = 0 or C.cd_und = @ag) How can I break this logic? I'm trying to break this code in various tables, and this part highlited in blue above, its the part that i don't got any breakthrough...here its a example of my code in SAS to solve this: /************************************************************************
-- exec spu_EstoqueContratoCredito_CREDIMASTER '20210217', '20210816', 78
--Rorine Name blabla
CREATE procedure [dbo].[spu_EstoqueContratoCredito_CREDIMASTER] (
@dt_ini date = null,
@dt_fim date = null,
@ag smallint = 0,
@cd_pst int = null
)
as
begin
************************************************************************/
/*implementação no dataset variaveisProcedureCREDIMASTER*/
%LET dt_ini='17FEB2021'd;
%LET dt_fim='16AUG2021'd;
%LET ag=78;
%LET cd_pst=.;
data parametrosCREDIMASTER;
format dataInicio dataFim date9.;
dt_ini = &dt_ini;
dataFim = &dt_fim;
agencia = &ag;
codPostal = &cd_pst;
run;
/******************************************************************************************
set nocount on
declare @nr_ctr integer, @nr_adl tinyint, @cd_emp tinyint, @cd_und smallint,
@sg_mod varchar(10), @cd_cli integer, @nr_nosnr numeric, @nr_sqnos tinyint,
@vr_prm decimal(18,2), @sd_atl decimal(18,2), @comp date, @ultdia date,
@qt_pcl_atr smallint, @vr_dev decimal(18,2), @dt_ven date, @database date,
@dataref varchar(8), @idx tinyint, @sg_gp_ctb varchar(10), @vr_dev_cbp decimal(18,2)
declare @n int,
@count int
if exists(select 1 from tempdb..sysobjects
where name = '#Contratos' and type = 'U'
)
drop table #Contratos
**************************************************************************************************/
data declareCREDIMASTER;
set parametrosCREDIMASTER;
length nr_ctr 8
nr_adl 8
cd_emp 8
cd_und 8
sg_mod $10
cd_cli 8
nr_nosnr 8
nr_sqnos 8
vr_prm 8
sd_atl 8
comp 8
ultdia 8
qt_pcl_atr 8
vr_dev 8
dt_ven 8
database 8
dataref $8
idx 8
sg_gp_ctb $10
vr_dev_cbp 8;
format comp date9.
ultdia date9.
dt_ven date9.
database date9.;
run;
/******************************************************************************************
PROC SQL
insert into tmpContratos
select
G.dt_gp_ctb,
G.nr_ctr,
G.nr_adl,
G.cd_emp,
G.cd_und,
G.sg_mod,
G.cd_cli,
Convert(varchar,G.nr_ctr)+dbo.f_number(G.nr_adl,3),
RTrim(M.ds_mod),
C.cd_cc,
case when (U.cd_ag_sup is null) then C.cd_und else C.cd_und/100 end,
case when (U.cd_ag_sup is not null) then right(C.cd_und,2) else '00' end,
C.cd_cli,
P.nr_cic,
C.dt_ope,
C.vr_pcp,
0.00,
G.sd_atl_ctb_d0,
C.qt_tit,
0,
c.dt_ope,
(select case when datediff(d,min(tiab.dt_ven),@dt_fim) >0 then datediff(d,min(tiab.dt_ven),@dt_fim) else 0 end diasatraso from linked_clust02.TB_CREDIMASTER.dbo.t402tiab tiab where tiab.cd_cli = c.cd_cli and tiab.nr_ctr = C.nr_ctr and tiab.nr_adl = C.n
r_adl and tiab.sg_mod = C.sg_mod) qt_dd_atr_dc,
(select MIN(DT_INC_GP) from linked_clust02.TB_CREDIMASTER.dbo.t402cogc where cd_cli = c.cd_cli and nr_ctr = C.nr_ctr and nr_adl = C.nr_adl and sg_mod = C.sg_mod and substring(sg_gp_ctb,1,3) = 'CBP') dataCBP,
case m.sg_lin_ger when 'EMP' THEN C.cd_und ELSE 0 END cd_und,
C.cd_usu_atu,
(select count(*) from linked_clust02.TB_CREDIMASTER.dbo.t402titu t
where c.cd_emp=t.cd_emp and c.cd_und=t.cd_und
and c.cd_cli=t.cd_cli and c.nr_ctr=t.nr_ctr
and c.nr_adl=t.nr_adl and c.sg_mod=t.sg_mod and t.dt_lqd is not null) parcPagas,
(CASE
WHEN C.id_sit_ctr='AT' or C.id_sit_ctr='RN' THEN
'ATIVO'
WHEN C.id_sit_ctr='EX' or C.id_sit_ctr='IN'THEN
'CANCELADA'
END) situacao
,ga.vr_gar valorImovel
,'NAO SE APLICA' matCadOperacao
,C.dt_ope dataCadOperacao
,'NAO SE APLICA' faseOperacao
,(select top 1 t.vr_tit from linked_clust02.TB_CREDIMASTER.dbo.t402titu t
where c.cd_emp=t.cd_emp and c.cd_und=t.cd_und
and c.cd_cli=t.cd_cli and c.nr_ctr=t.nr_ctr
and c.nr_adl=t.nr_adl and c.sg_mod=t.sg_mod
) valorParcela
,(select t.dt_ven from linked_clust02.TB_CREDIMASTER.dbo.t402titu t
where c.cd_emp=t.cd_emp and c.cd_und=t.cd_und
and c.cd_cli=t.cd_cli and c.nr_ctr=t.nr_ctr
and c.nr_adl=t.nr_adl and c.sg_mod=t.sg_mod
and c.qt_tit=1) dataVencParcela
,(SELECT top 1 vw.sd_tmd_exs FROM vw_t401rsdc vw With (NOLOCK)
where vw.cd_und=C.cd_und and vw.cd_cc=C.cd_cc and vw.dt_mov between @dt_ini and @dt_fim order by vw.dt_mov desc) excessoLimite_subSelect
,'NAO SE APLICA' sistAmort,
C.sg_gp_ctb,
null
from linked_clust02.TB_CREDIMASTER.dbo.t402cogc G With (NOLOCK)
join linked_clust02.TB_CREDIMASTER.dbo.t402cont C With (NOLOCK)
on (G.cd_emp = C.cd_emp
and G.cd_und = C.cd_und
and G.cd_cli = C.cd_cli
and G.nr_ctr = C.nr_ctr
and G.nr_adl = C.nr_adl
and G.sg_mod = C.sg_mod)
join linked_clust02.TB_CREDIMASTER.dbo.t402moda M With (NOLOCK) on (C.sg_mod = M.sg_mod)
join linked_clust02.tb_basico.dbo.t400pess P With (NOLOCK) on (C.cd_cli = P.cd_cli)
join linked_clust02.tb_basico.dbo.t400unop U With (NOLOCK) on (U.cd_emp = C.cd_emp and C.cd_und = U.cd_und_fis)
left join tfs_garantias..t472gara ga With (NOLOCK)
on (ga.cd_cli = C.cd_cli and ga.ds_gar='ALIMO')
where M.sg_mod in ('CAPGIRO','CAPGIROF','GIRORAP','GIRORAPF','RENEGPJ','RENEGPJF',
'CHQCBPPF','CHQCBPPJ','EXSCBPPF','EXSCBPPF','ADCBPPJ','ADCBPPF')
and G.sg_gp_ctb <> 'CONTRATO' and G.sd_atl_ctb_d0 > 0
and G.dt_gp_ctb = @dt_fim
and (isnull(@ag,0) = 0 or C.cd_und = @ag)
**************************************************************************************************/
PROC SQL;
TITLE 'TabelaG';
TITLE2 'tabela T402COGC';
CREATE TABLE tabelaG AS
SELECT
cd_cli,
nr_ctr,
nr_adl,
cd_emp,
cd_und,
sg_mod,
dt_gp_ctb,
cat(nr_ctr,nr_adl) AS nrCtr_nrAdl
FROM STGCREDM.STG_T402COGC
ORDER BY cd_emp,cd_und,cd_cli,nr_ctr,nr_adl,sg_mod;
PROC SQL;
TITLE 'TabelaC';
TITLE2 'tabela T402CONT';
CREATE TABLE tabelaC AS
SELECT
nr_ctr,
nr_adl,
cd_emp,
cd_und,
sg_mod,
cd_cli,
qt_tit,
datepart(dt_ope) format=date9. AS dt_ope,
vr_pcp
FROM STGCREDM.STG_T402CONT
ORDER BY cd_emp,cd_und,cd_cli,nr_ctr,nr_adl,sg_mod;
PROC SQL;
TITLE 'TabelaTIAB';
TITLE2 'tabela T402TIAB';
CREATE TABLE tabelaTIAB AS
SELECT
tiab.nr_ctr,
datepart(tiab.dt_ven) format=date9. as dt_ven,
tiab.cd_cli,
tiab.nr_adl,
tiab.sg_mod,
qt_dd_atr_dc,
SELECT CASE WHEN
INTCK('day',tiab.dt_ven,"&dt_fim") > 0 THEN INTCK('day',tiab.dt_ven
FROM STGCREDM.STG_T402TIAB AS tiab
INNER JOIN (SELECT cd_cli, nr_ctr, nr_adl, sg_mod FROM work.tabelac) AS C ON
tiab.cd_cli = c.cd_cli AND tiab.nr_ctr = C.nr_ctr AND tiab.nr_adl = C.nr_adl AND tiab.sg_mod = C.sg_mod
ORDER BY tiab.dt_ven;
PROC SQL;
TITLE 'TabelaMODA';
TITLE2 'tabela T402MODA';
CREATE TABLE tabelaMODA AS
SELECT
cd_und,
M.sg_mod,
TRIM(M.ds_mod) as ds_mod,
CASE WHEN M.sg_lin_ger = 'EMP' THEN C.cd_und ELSE 0 END AS COD_UNI
FROM STGCREDM.STG_T402MODA AS M
INNER JOIN (SELECT sg_mod, cd_und FROM work.tabelac) AS C ON M.sg_mod = C.sg_mod;
QUIT; If anyone could help or give any resource to this I will be grateful.
... View more
