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.
There is really too much code here for this type of forum.
Do you know what the T-SQL code does?
Do you know the overall purpose? In general it is better to make a new program that fits the goal instead of trying to do a line by line translation.
If not then you need to first ask someone that speaks that dialect of SQL to explain what the code is trying to do.
First thing when given strangely formatted code is to clean up the formatting so it makes more sense:
So this first blue text
(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
Looks like some type on in-line subquery. That will only work in SAS if the result of that query returns one observation of only one variable. So the only strange thing there is the DATEDIF() function. So you need to understand what DATEDIF() does and try to generate the same thing with SAS code. Perhaps it is like the INTNX() function? But the CASE clause could probably just be replaced with a MAX(,) function call in SAS. (other SQL dialects sometimes have something called GREATER() to mimic the SAS MAX(,) function).
There are some parts of the SAS code that looks strange.
For example unless you meant to add NEW variables the order of the SET and LENGTH statements are backwards in this step. You cannot change the length of the character variables once they have been defined (fin this case when the variable exist in the dataset in SET statement). And you normally do not need to change the length of the numeric variables since you should just store the full 8 bytes needed to store the 64-bit binary floating point numbers SAS uses.
data declareCREDIMASTER;
set parametrosCREDIMASTER;
length nr_ctr 8
nr_adl 8
cd_emp 8
...
And in this one:
data parametrosCREDIMASTER;
format dataInicio dataFim date9.;
dt_ini = &dt_ini;
dataFim = &dt_fim;
agencia = &ag;
codPostal = &cd_pst;
run;
Don't put the FORMAT statement before defining the variables. That will just mess up the variable order in the dataset.
Also you are attaching the DATE format to a variable, dataInicio, that you don't assign any value to. And not attaching it to one where you are assigning it a date value. So the date values in DT_INI will not print in a way that a human will recognize as a date value.
Why do you need to convert it? You can run T-SQL unchanged from SAS with SQL Passthrough.
hi, i don't know this method, can you provide some resource for me to learn about it?
If you are creating a table in SAS:
libname SQLSRVR ODBC noprompt = "<SQL server connection string>";
proc sql;
connect using SQLSRVR;
create table Want as
select * from connection to SQLSRVR
(<-Put your SQL Server query here->)
;
quit;
If you just want to run a series of T-SQL statements:
libname SQLSRVR ODBC noprompt = "<SQL server connection string>";
proc sql;
connect using SQLSRVR;
execute (<-Put your SQL Server queries here->
) by SQLSRVR;
;
quit;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.