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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: