I cant join since there is no key between transactions and promotion ID, I have to make a column by each promotion in the catalog and evaluate the case when calculation, so that the promotion variable will be binary (1 = Sales was made between the promotion range, 0 = Sale was not made in promotion range), what I did manually was this but I want to know if I can make it automatic with thousand more promotions PROC SQL;
CREATE TABLE WORK.QUERY_FOR_ANDREASAMPLEXXXXXX AS
SELECT t1.Sucursal,
t1.Producto,
t1.Fecha,
t1.Demanda_positiva,
t1.Demanda_negativa,
t1.Talla,
t1.Precio,
t1.Demanda,
t1.Descuento,
/* PROMOCION1 */
(CASE WHEN (t1.Fecha >= "18Jan2019"d and t1.Fecha <= "18May2019"d) THEN 1 ELSE 0 END) LABEL=
"catalogocalzadoprimavera2019mex" AS PROMOCION1,
/* PROMOCION14 */
(CASE WHEN (t1.Fecha >= "18Feb2019"d and t1.Fecha <= "18May2019"d) THEN 1 ELSE 0 END) LABEL=
"catálogoiubellezaintegralprimavera2019eua" AS PROMOCION14,
/* PROMOCION2 */
(CASE WHEN (t1.Fecha >= "18Jan2019"d and t1.Fecha <= "18May2019"d) THEN 1 ELSE 0 END) LABEL=
"catalogovestirinteriorprimavera2019eua" AS PROMOCION2,
/* PROMOCION3 */
(CASE WHEN (t1.Fecha >= "18Jan2019"d and t1.Fecha <= "18May2019"d) THEN 1 ELSE 0 END) LABEL=
"catalogovestirexteriorprimavera2019eua" AS PROMOCION3,
/* PROMOCION4 */
(CASE WHEN (t1.Fecha >= "18Jan2019"d and t1.Fecha <= "18May2019"d) THEN 1 ELSE 0 END) LABEL=
"catalogocalzadoprimavera2019eua" AS PROMOCION4,
/* PROMOCION5 */
(CASE WHEN (t1.Fecha >= "18Jan2019"d and t1.Fecha <= "18May2019"d) THEN 1 ELSE 0 END) LABEL=
"catalogobellezaintegralprimavera2019mex" AS PROMOCION5,
/* PROMOCION6 */
(CASE WHEN (t1.Fecha >= "18Jan2019"d and t1.Fecha <= "18May2019"d) THEN 1 ELSE 0 END) LABEL=
"catalogovestirexteriorprimavera2019mex" AS PROMOCION6,
/* PROMOCION7 */
(CASE WHEN (t1.Fecha >= "18Jan2019"d and t1.Fecha <= "18May2019"d) THEN 1 ELSE 0 END) LABEL=
"catalogovestirinteriorprimavera2019mex" AS PROMOCION7,
/* PROMOCION8 */
(CASE WHEN (t1.Fecha >= "09Jan2019"d and t1.Fecha <= "09Mar2019"d) THEN 1 ELSE 0 END) LABEL=
"catálogooutlet12019méxico" AS PROMOCION8,
/* PROM0CION9 */
(CASE WHEN (t1.Fecha >= "23Dec2018"d and t1.Fecha <= "23Feb2019"d) THEN 1 ELSE 0 END) LABEL=
"lifestyles&sportenero/febrero" AS PROM0CION9,
/* PROMOCION10 */
(CASE WHEN (t1.Fecha >= "23Jan2019"d and t1.Fecha <= "23Feb2019"d) THEN 1 ELSE 0 END) LABEL=
"catalogofuerzayestiloenero-febrero2019" AS PROMOCION10,
/* PROMOCION111 */
(CASE WHEN (t1.Fecha >= "16Jan2019"d and t1.Fecha <= "16Mar2019"d) THEN 1 ELSE 0 END) LABEL=
"catálogooutlet12019eua" AS PROMOCION111,
/* PROMOCION12 */
(CASE WHEN (t1.Fecha >= "06Jan2019"d and t1.Fecha <= "06Apr2019"d) THEN 1 ELSE 0 END) LABEL=
"promotorprofesionaleindustrial" AS PROMOCION12,
/* PROMOCION13 */
(CASE WHEN (t1.Fecha >= "16Feb2019"d and t1.Fecha <= "16Mar2019"d) THEN 1 ELSE 0 END) LABEL=
"promotorbotasybotines" AS PROMOCION13,
/* PROMOCION15 */
(CASE WHEN (t1.Fecha >= "08Feb2019"d and t1.Fecha <= "08Jun2019"d) THEN 1 ELSE 0 END) LABEL=
"lifestyles&urbano2019marzoeua" AS PROMOCION15,
/* PROMOCION16 */
(CASE WHEN (t1.Fecha >= "01Feb2019"d and t1.Fecha <= "01Jun2019"d) THEN 1 ELSE 0 END) LABEL=
"catalogofuerzayestilomarzo2019" AS PROMOCION16,
/* PROMOCION17 */
(CASE WHEN (t1.Fecha >= "01Feb2019"d and t1.Fecha <= "01Jun2019"d) THEN 1 ELSE 0 END) LABEL=
"catálogourbanoprimavera2019" AS PROMOCION17,
/* PROMOCION18 */
(CASE WHEN (t1.Fecha >= "24Feb2019"d and t1.Fecha <= "24Aug2019"d) THEN 1 ELSE 0 END) LABEL=
"catalogotropicalprimavera2019mexico" AS PROMOCION18,
/* PROMOCION19 */
(CASE WHEN (t1.Fecha >= "24Feb2019"d and t1.Fecha <= "24Aug2019"d) THEN 1 ELSE 0 END) LABEL=
"catalogosushineprimavera2019usa" AS PROMOCION19,
/* PROMOCION20 */
(CASE WHEN (t1.Fecha >= "04Mar2019"d and t1.Fecha <= "04May2019"d) THEN 1 ELSE 0 END) LABEL=
"catálogooutlet22019méxico" AS PROMOCION20,
/* PROMOCION21 */
(CASE WHEN (t1.Fecha >= "23Feb2019"d and t1.Fecha <= "23Mar2019"d) THEN 1 ELSE 0 END) LABEL=
"programaestelar1erbimestreprimavera2019eua" AS PROMOCION21,
/* PROMOCION22 */
(CASE WHEN (t1.Fecha >= "23Jan2019"d and t1.Fecha <= "23Mar2019"d) THEN 1 ELSE 0 END) LABEL=
"programaestelar1erbimestreprimavera2019" AS PROMOCION22,
/* PROMOCION23 */
(CASE WHEN (t1.Fecha >= "04Mar2019"d and t1.Fecha <= "04May2019"d) THEN 1 ELSE 0 END) LABEL=
"catálogooutlet22019eua" AS PROMOCION23,
/* PROMOCION24 */
(CASE WHEN (t1.Fecha >= "18Mar2019"d and t1.Fecha <= "18May2019"d) THEN 1 ELSE 0 END) LABEL=
"programaestelar2ndobimestreprimavera2019eua" AS PROMOCION24,
/* PROMOCION25 */
(CASE WHEN (t1.Fecha >= "11Mar2019"d and t1.Fecha <= "11May2019"d) THEN 1 ELSE 0 END) LABEL=
"catálogooutlet32019méxico" AS PROMOCION25,
/* PROMOCION26 */
(CASE WHEN (t1.Fecha >= "18Apr2019"d and t1.Fecha <= "18May2019"d) THEN 1 ELSE 0 END) LABEL=
"catálogooutlet32019eua" AS PROMOCION26,
/* PROMOCION27 */
(CASE WHEN (t1.Fecha >= "24Apr2019"d and t1.Fecha <= "24Aug2019"d) THEN 1 ELSE 0 END) LABEL=
"catálogoprofesionaleindustrial2019" AS PROMOCION27,
/* PROMOCION11 */
(CASE WHEN (t1.Fecha >= "18Jan2019"d and t1.Fecha <= "18May2019"d) THEN 1 ELSE 0 END) LABEL=
"catalogocalzadoprimavera2019mex" AS PROMOCION11
FROM SASHELP.ANDREASAMPLEXXXXXX t1;
QUIT;
... View more