topic Re: Filter data based on current weekday in New SAS User

Filter data based on current weekday

mohtamimi94 — Wed, 15 Dec 2021 12:25:51 GMT

I'm trying to do a smart filter where it filter the last 3 days when it works on Sunday and it filter the last 2 days when it works on any other day but it gives me empty output always

Code:

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_BILLING_CONTRACTS_DETA AS
SELECT t1.PROCESS_DATE,
t1.CUST_NUMBER,
t1.CUSTCODE,
t1.CONTRACT_TYPE,
t1.SUB_MARKET,
t1.TITLE,
t1.F_NAME,
t1.M_NAME1,
t1.M_NAME2,
t1.L_NAME,
t1.M_NAME_AS_IS,
t1.NATIONALITY,
t1.ID_TYPE,
t1.ID_NUMBER,
t1.NATIONAL_NUMBER,
t1.SUBS_NATIONAL_NO,
t1.COMPANY_RGT_NO,
t1.GENDER,
t1.BIRTHDATE,
t1.PO_BOX,
t1.CCZIP,
t1.ALTERNATIVE_NO1,
t1.ALTERNATIVE_NO2,
t1.CCEMAIL,
t1.CCADDR2,
t1.CCADDR3,
t1.GOVERNORATE,
t1.CCCITY,
t1.AREA,
t1.BILLCYCLE,
t1.ACCOUNT_TYPE,
t1.MSISDN,
t1.CO_ID,
t1.PACKAGE_NAME,
t1.CONTRACT_NUMBER,
t1.CUSTOMER_ACTIVATION_DATE,
t1.ACTIVATION_DATE,
t1.CONTRACT_CHANNEL_TYPE,
t1.CONTRACT_CHANNEL_DESC,
t1.USER_NAME,
t1.CREDIT_CATEGORY,
t1.PACKAGE_MARKET,
/* Check */
(CASE
WHEN datepart(t1.ACTIVATION_DATE) = datepart(t1.CUSTOMER_ACTIVATION_DATE) THEN "New Customer"
ELSE "Old customer"
END) AS Check
FROM BI_LEB.BILLING_CONTRACTS_DETAILS t1
WHERE t1.CONTRACT_TYPE = 'POSTPAID' AND t1.ACCOUNT_TYPE = 'Flat Account' AND t1.CONTRACT_CHANNEL_DESC NOT =
'IT Support Testing' AND (CALCULATED Check) = 'New Customer' AND t1.ACTIVATION_DATE BETWEEN
(CASE
WHEN weekday(today()) = 1 THEN TODAY()-3
WHEN weekday(today()) > 1 THEN TODAY()-1
END)) AND today();
QUIT;

Re: Filter data based on current weekday

Kurt_Bremser — Wed, 15 Dec 2021 12:57:59 GMT

In other parts of your query, you use the DATEPART function on ACTIVATION_DATE, suggesting that it is in fact a datetime value; therefore, comparing it to a date value will fail.

Re: Filter data based on current weekday

Tom — Wed, 15 Dec 2021 15:45:01 GMT

Looks like the issue is that the variable that has the name t1.ACTIVATION_DATE probably actually has DATETIME values (despite its name) instead of DATE values. So you need to use the DATEPART() function if you want to compare its values to date values.

  AND datepart(t1.ACTIVATION_DATE)
      BETWEEN (CASE WHEN weekday(today()) = 1 THEN TODAY()-3
                    WHEN weekday(today()) > 1 THEN TODAY()-1
               END)
      AND today()

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_BILLING_CONTRACTS_DETA AS
SELECT
   t1.PROCESS_DATE
  ,t1.CUST_NUMBER
  ,t1.CUSTCODE
  ,t1.CONTRACT_TYPE
  ,t1.SUB_MARKET
  ,t1.TITLE
  ,t1.F_NAME
  ,t1.M_NAME1
  ,t1.M_NAME2
  ,t1.L_NAME
  ,t1.M_NAME_AS_IS
  ,t1.NATIONALITY
  ,t1.ID_TYPE
  ,t1.ID_NUMBER
  ,t1.NATIONAL_NUMBER
  ,t1.SUBS_NATIONAL_NO
  ,t1.COMPANY_RGT_NO
  ,t1.GENDER
  ,t1.BIRTHDATE
  ,t1.PO_BOX
  ,t1.CCZIP
  ,t1.ALTERNATIVE_NO1
  ,t1.ALTERNATIVE_NO2
  ,t1.CCEMAIL
  ,t1.CCADDR2
  ,t1.CCADDR3
  ,t1.GOVERNORATE
  ,t1.CCCITY
  ,t1.AREA
  ,t1.BILLCYCLE
  ,t1.ACCOUNT_TYPE
  ,t1.MSISDN
  ,t1.CO_ID
  ,t1.PACKAGE_NAME
  ,t1.CONTRACT_NUMBER
  ,t1.CUSTOMER_ACTIVATION_DATE
  ,t1.ACTIVATION_DATE
  ,t1.CONTRACT_CHANNEL_TYPE
  ,t1.CONTRACT_CHANNEL_DESC
  ,t1.USER_NAME
  ,t1.CREDIT_CATEGORY
  ,t1.PACKAGE_MARKET
  ,CASE WHEN datepart(t1.ACTIVATION_DATE) = datepart(t1.CUSTOMER_ACTIVATION_DATE) THEN "New Customer"
        ELSE "Old customer"
   END AS Check
FROM BI_LEB.BILLING_CONTRACTS_DETAILS t1
WHERE t1.CONTRACT_TYPE = 'POSTPAID'
  AND t1.ACCOUNT_TYPE = 'Flat Account'
  AND t1.CONTRACT_CHANNEL_DESC NOT = 'IT Support Testing'
  AND (CALCULATED Check) = 'New Customer'
  AND datepart(t1.ACTIVATION_DATE)
      BETWEEN (CASE WHEN weekday(today()) = 1 THEN TODAY()-3
                    WHEN weekday(today()) > 1 THEN TODAY()-1
               END)
      AND today()
;
QUIT;

Re: Filter data based on current weekday

mohtamimi94 — Thu, 16 Dec 2021 08:25:53 GMT

Solved, thank you