topic Transform decode to sas language in SAS Programming

Transform decode to sas language

albribmun — Fri, 21 Jan 2022 13:21:23 GMT

I would like to know how to change "decode" and "to_char" to sas language. The query in question is the following. I am working the query with proc cas and fedSQL.

proc cas;

source MPG_toyota;

create table mkt.test1 as

SELECT

DIM_MARCAS.DESC_MRC_ES,

DIM_GAMAS.COD_GM_AGR,

/* DECODE(@Variable('PREFERRED_VIEWING_LOCALE'), 'en_GB', DECODE(NVL(DEV_DIM_OBJ_AGR.COD_AGR_OBJ_EN,'NULO'), 'NULO', DEV_DIM_OBJ_AGR.COD_AGR_OBJ_ES,DEV_DIM_OBJ_AGR.COD_AGR_OBJ_EN),DEV_DIM_OBJ_AGR.COD_AGR_OBJ_ES) , */

DIM_ORIGEN.DESC_ORIGEN,

DIM_CONCESIONARIOS.COD_CONC

FROM

MKT.DIM_MARCAS,

MKT.DIM_GAMAS,

(

select PK_AGR, COD_AGR_OBJ_ES, COD_AGR_OBJ_EN,AGR_OBJ_ES,AGR_OBJ_EN from mkt.dim_objeto_agr

) DEV_DIM_OBJ_AGR,

mkt.DIM_ORIGEN,

mkt.DIM_ESTADO_OPE_AGR,

/* GENE_MESES, */

/* GENE_MESES DATA_GENE_MESES_R_VENCIMIENTO, */

mkt.DIM_CONCESIONARIOS,

mkt.DIM_OBJETO,

mkt.REL_CONTRATO_VEHICULO_BI,

mkt.DIM_TMAIMG,

mkt.CONTRATOS_BI,

(

select 'FOPEFORM' AS CATEGORIA, pk_sit AS PK_ESTADO, COD_SIT AS COD_ESTADO, desc_sit_es AS desc_ESTADO, fk_est_ope_agr from MKT.dim_situacion

union

select 'RENTING', pk_sit_rent, codINDEs, descripcion, fk_est_ope_agr from MKT.dim_sit_rent

union

select 'VISPOLIZAS', PK_ESTADO_POL,cod_estado,desc_estado,fk_est_ope_agr from MKT.dim_estado_poliza

union

select 'FMANTF', PK_EST,EST_VEH,DES_VEH_ES, fk_est_ope_agr from MKT.dim_est_vehiculo

union

select 'FMANTR', PK_EST,EST_VEH,DES_VEH_ES, fk_est_ope_agr from MKT.dim_est_vehiculo

) DIM_ESTADO_OPE_DETALLE

WHERE

(DIM_OBJETO.PK_OBJ=REL_CONTRATO_VEHICULO_BI.FK_OBJ)

AND ( DEV_DIM_OBJ_AGR.PK_AGR=DIM_OBJETO.PK_OBJ )

AND ( CONTRATOS_BI.FK_CONC=DIM_CONCESIONARIOS.PK_CONC )

AND ( DIM_MARCAS.PK_MRC=DIM_GAMAS.FK_MRC )

AND ( DIM_TMAIMG.FK_GM=DIM_GAMAS.PK_GM )

AND ( DIM_ESTADO_OPE_DETALLE.FK_EST_OPE_AGR=DIM_ESTADO_OPE_AGR.PK_EST_OPE_AGR )

AND ( CONTRATOS_BI.FK_SITUACION=DIM_ESTADO_OPE_DETALLE.PK_ESTADO AND

CONTRATOS_BI.ORIGEN=DIM_ESTADO_OPE_DETALLE.CATEGORIA )

AND ( CONTRATOS_BI.CODOPERA=REL_CONTRATO_VEHICULO_BI.CODOPERA )

AND (DIM_TMAIMG.PK_TMAIMG=REL_CONTRATO_VEHICULO_BI.FK_TMAIMG )

/* AND ( REL_CONTRATO_VEHICULO_BI.FECREVTO=GENE_DIAS_R_VENCIMIENTO.PK_DIA ) */

AND ( DIM_ORIGEN.PK_ORIGEN=CONTRATOS_BI.FK_ORIGEN );

endsource;

fedSQL.execDirect / query=MPG_toyota;

quit;

Re: Transform decode to sas language

ballardw — Fri, 21 Jan 2022 16:22:26 GMT

Since you are asking about DECODE and TO_CHAR of SAS users you should at least consider describing what these do.

I might guess that To_char converts numeric values to character, which would be Put(variablename, formattouse.) but since your example code does not use To_char that is a complete guess.

@albribmun wrote:

I would like to know how to change "decode" and "to_char" to sas language. The query in question is the following. I am working the query with proc cas and fedSQL.

proc cas;

    source MPG_toyota;

create table mkt.test1 as

SELECT

DIM_MARCAS.DESC_MRC_ES,

DIM_GAMAS.COD_GM_AGR,

/* DECODE(@Variable('PREFERRED_VIEWING_LOCALE'), 'en_GB', DECODE(NVL(DEV_DIM_OBJ_AGR.COD_AGR_OBJ_EN,'NULO'), 'NULO', DEV_DIM_OBJ_AGR.COD_AGR_OBJ_ES,DEV_DIM_OBJ_AGR.COD_AGR_OBJ_EN),DEV_DIM_OBJ_AGR.COD_AGR_OBJ_ES) , */

DIM_ORIGEN.DESC_ORIGEN,

DIM_CONCESIONARIOS.COD_CONC

FROM

  MKT.DIM_MARCAS,

MKT.DIM_GAMAS,

(

  select PK_AGR, COD_AGR_OBJ_ES, COD_AGR_OBJ_EN,AGR_OBJ_ES,AGR_OBJ_EN from mkt.dim_objeto_agr

) DEV_DIM_OBJ_AGR,

mkt.DIM_ORIGEN,

mkt.DIM_ESTADO_OPE_AGR,

/*   GENE_MESES, */

/*   GENE_MESES DATA_GENE_MESES_R_VENCIMIENTO, */

mkt.DIM_CONCESIONARIOS,

mkt.DIM_OBJETO,

mkt.REL_CONTRATO_VEHICULO_BI,

mkt.DIM_TMAIMG,

mkt.CONTRATOS_BI,

(

select 'FOPEFORM' AS CATEGORIA, pk_sit AS PK_ESTADO, COD_SIT AS COD_ESTADO, desc_sit_es AS desc_ESTADO, fk_est_ope_agr from MKT.dim_situacion

union

select 'RENTING', pk_sit_rent, codINDEs, descripcion, fk_est_ope_agr from MKT.dim_sit_rent

union

select 'VISPOLIZAS', PK_ESTADO_POL,cod_estado,desc_estado,fk_est_ope_agr from MKT.dim_estado_poliza

union

select 'FMANTF', PK_EST,EST_VEH,DES_VEH_ES, fk_est_ope_agr from MKT.dim_est_vehiculo

union

select 'FMANTR', PK_EST,EST_VEH,DES_VEH_ES, fk_est_ope_agr from MKT.dim_est_vehiculo

) DIM_ESTADO_OPE_DETALLE

WHERE

  (DIM_OBJETO.PK_OBJ=REL_CONTRATO_VEHICULO_BI.FK_OBJ)

AND ( DEV_DIM_OBJ_AGR.PK_AGR=DIM_OBJETO.PK_OBJ )

AND ( CONTRATOS_BI.FK_CONC=DIM_CONCESIONARIOS.PK_CONC )

AND ( DIM_MARCAS.PK_MRC=DIM_GAMAS.FK_MRC )

AND ( DIM_TMAIMG.FK_GM=DIM_GAMAS.PK_GM )

AND ( DIM_ESTADO_OPE_DETALLE.FK_EST_OPE_AGR=DIM_ESTADO_OPE_AGR.PK_EST_OPE_AGR )

AND ( CONTRATOS_BI.FK_SITUACION=DIM_ESTADO_OPE_DETALLE.PK_ESTADO AND

CONTRATOS_BI.ORIGEN=DIM_ESTADO_OPE_DETALLE.CATEGORIA )

AND ( CONTRATOS_BI.CODOPERA=REL_CONTRATO_VEHICULO_BI.CODOPERA )

AND (DIM_TMAIMG.PK_TMAIMG=REL_CONTRATO_VEHICULO_BI.FK_TMAIMG )

/*   AND ( REL_CONTRATO_VEHICULO_BI.FECREVTO=GENE_DIAS_R_VENCIMIENTO.PK_DIA ) */

AND ( DIM_ORIGEN.PK_ORIGEN=CONTRATOS_BI.FK_ORIGEN );

    endsource;

    fedSQL.execDirect / query=MPG_toyota;

quit;

Re: Transform decode to sas language

Reeza — Fri, 21 Jan 2022 16:43:43 GMT

DECODE can be recoded to CASE WHEN but you need to know the possible input values which is not present in the code.

An alternative approach in SAS would be an INFORMAT but you would need to predefine that outside of this procedure.

For TO_CHAR, it depends on usage.