Re: Help to convert T-SQL to SAS base lang

FelipeVerissim0 · Posted 06-13-2023 09:15 AM

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.

Reeza · Posted 06-13-2023 10:42 AM

This is possibly a good use for ChatGPT.

Tom · Posted 06-13-2023 11:26 AM

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.

FelipeVerissim0 · Posted 06-13-2023 02:15 PM

@Tom and @Reeza, thanks for the help and tips, i will keep trying translate this lines, it's gonna be hard its a 500 rows of t-sql script

SASKiwi · Posted 06-13-2023 04:18 PM

Why do you need to convert it? You can run T-SQL unchanged from SAS with SQL Passthrough.

FelipeVerissim0 · Posted 01-11-2024 10:03 AM

hi, i don't know this method, can you provide some resource for me to learn about it?

SASKiwi · Posted 01-11-2024 02:09 PM

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;

Help to convert T-SQL to SAS base lang