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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.