About Eugenio211

Eugenio211 · ‎01-11-2021

here is the example data. the objective is to find 'campaign_converted' where campaign_converted is loandate ge campaign_date(n) CUSTOMER campaign_date1 campaign_date2 campaign_date3 campaign_date4 LOANDATE CAMPAIGN_CONVERTED CUSTOMER 1 04/09/2019 05/31/2019 CUSTOMER 2 07/02/2019 08/14/2019 CUSTOMER 3 09/19/2019 08/20/2020 10/22/2020 12/09/2020 CUSTOMER 4 04/09/2019 08/20/2020 10/22/2020 12/09/2020 CUSTOMER 5 06/23/2020 09/25/2020 11/11/2020 CUSTOMER 6 11/11/2020 CUSTOMER 7 07/02/2019 08/16/2019 CUSTOMER 8 07/17/2020 10/22/2020 12/09/2020 CUSTOMER 9 04/09/2019 07/05/2019 CUSTOMER 10 04/09/2019 05/24/2019 CUSTOMER 11 06/23/2020 CUSTOMER 12 06/23/2020 09/25/2020 11/11/2020 CUSTOMER 13 04/09/2019 08/20/2020 12/09/2020 CUSTOMER 14 04/09/2019 04/11/2019 CUSTOMER 15 07/17/2020 10/22/2020 CUSTOMER 16 04/09/2019 12/23/2019 CUSTOMER 17 04/09/2019 05/18/2019 CUSTOMER 18 04/09/2019 05/28/2019 CUSTOMER 19 04/09/2019 04/29/2019 CUSTOMER 20 10/22/2020 12/09/2020 CUSTOMER 21 12/09/2020 12/30/2020 CUSTOMER 22 04/09/2019 07/10/2019 CUSTOMER 23 04/09/2019 04/13/2019 CUSTOMER 24 04/09/2019 04/20/2019 CUSTOMER 25 04/09/2019 08/08/2020 CUSTOMER 26 04/09/2019 08/30/2019 CUSTOMER 27 04/09/2019 12/22/2019 CUSTOMER 28 07/02/2019 08/06/2019 CUSTOMER 29 04/09/2019 02/04/2020 CUSTOMER 30 12/09/2020 CUSTOMER 31 04/09/2019 06/25/2019 CUSTOMER 32 06/23/2020 09/25/2020 11/11/2020 CUSTOMER 33 06/23/2020 09/25/2020 10/07/2020 CUSTOMER 34 04/09/2019 04/15/2019 CUSTOMER 35 04/09/2019 05/10/2019 CUSTOMER 36 04/09/2019 07/25/2019 CUSTOMER 37 04/09/2019 08/20/2020 10/22/2020 12/09/2020 CUSTOMER 38 02/18/2019 06/28/2019 CUSTOMER 39 04/09/2019 05/11/2019 CUSTOMER 40 07/02/2019 08/15/2019 CUSTOMER 41 07/02/2019 CUSTOMER 42 06/23/2020 11/11/2020 CUSTOMER 43 04/09/2019 11/02/2019 CUSTOMER 44 04/09/2019 08/20/2020 10/22/2020 12/09/2020 CUSTOMER 45 08/20/2020 10/22/2020 12/09/2020 12/07/2020 CUSTOMER 46 07/02/2019 CUSTOMER 47 04/09/2019 11/22/2019

Eugenio211 · ‎01-11-2021

Thanks for the response, but this is what I am currently using. I am trying to see if this can be done using 'array'.

Eugenio211 · ‎01-11-2021

here is the current steps I am using: data can_approvals3; set can_approvals2; format CAMPAIGN_CONVERTED mmddyy.; if LoanDate >= campaign_date1 and campaign_date2 = . and campaign_date3 = . and campaign_date4 = . then CAMPAIGN_CONVERTED = Campaign_date1; else if LoanDate >= campaign_date1 and LoanDate < campaign_date2 and campaign_date3 = . and campaign_date4 = . then CAMPAIGN_CONVERTED = Campaign_date1; else if LoanDate >= campaign_date1 and LoanDate < campaign_date2 and LoanDate < campaign_date3 and LoanDate < campaign_date4 then CAMPAIGN_CONVERTED = Campaign_date1; else if LoanDate >= campaign_date2 and campaign_date3 = . and campaign_date4 = . then CAMPAIGN_CONVERTED = campaign_date2; else if LoanDate >= campaign_date2 and LoanDate < campaign_date3 and campaign_date4 = . then CAMPAIGN_CONVERTED = campaign_date2; else if LoanDate >= campaign_date3 and campaign_date4 =. then CAMPAIGN_CONVERTED = campaign_date3; else if LoanDate >= campaign_date3 and LoanDate < campaign_date4 then CAMPAIGN_CONVERTED = campaign_date3; else if LoanDate >= campaign_date4 then CAMPAIGN_CONVERTED = campaign_date4; run;

Eugenio211 · ‎01-11-2021

hello - Hope everyone is ok. I am posting this a new as to gather new help from other programmers. New Campaign dates will be added each month. I am asking for help to write a query to avoid with too much 'if else..then do' statements, I thank you for those who responded and offer suggestions before. For example. 'Customer 45' - if the loan_date is 12/07/2020 then the 'campaign_converted' should be '10/22/2020'. 'Customer 37' - if the loan_date is 09/23/2020 then 'campaign_converted' should be '08/20/2020' And if there is no loan_date then 'campaign_converted is blank. CUSTOMER campaign_date1 campaign_date2 campaign_date3 campaign_date4 LOANDATE CAMPAIGN_CONVERTED CUSTOMER 1 04/09/2019 05/31/2019 CUSTOMER 2 07/02/2019 08/14/2019 CUSTOMER 3 09/19/2019 08/20/2020 10/22/2020 12/09/2020 CUSTOMER 4 04/09/2019 08/20/2020 10/22/2020 12/09/2020 CUSTOMER 5 06/23/2020 09/25/2020 11/11/2020 CUSTOMER 6 11/11/2020 CUSTOMER 7 07/02/2019 08/16/2019 CUSTOMER 8 07/17/2020 10/22/2020 12/09/2020 CUSTOMER 9 04/09/2019 07/05/2019 CUSTOMER 10 04/09/2019 05/24/2019 CUSTOMER 11 06/23/2020 CUSTOMER 12 06/23/2020 09/25/2020 11/11/2020 CUSTOMER 13 04/09/2019 08/20/2020 12/09/2020 CUSTOMER 14 04/09/2019 04/11/2019 CUSTOMER 15 07/17/2020 10/22/2020 CUSTOMER 16 04/09/2019 12/23/2019 CUSTOMER 17 04/09/2019 05/18/2019 CUSTOMER 18 04/09/2019 05/28/2019 CUSTOMER 19 04/09/2019 04/29/2019 CUSTOMER 20 10/22/2020 12/09/2020 CUSTOMER 21 12/09/2020 12/30/2020 CUSTOMER 22 04/09/2019 07/10/2019 CUSTOMER 23 04/09/2019 04/13/2019 CUSTOMER 24 04/09/2019 04/20/2019 CUSTOMER 25 04/09/2019 08/08/2020 CUSTOMER 26 04/09/2019 08/30/2019 CUSTOMER 27 04/09/2019 12/22/2019 CUSTOMER 28 07/02/2019 08/06/2019 CUSTOMER 29 04/09/2019 02/04/2020 CUSTOMER 30 12/09/2020 CUSTOMER 31 04/09/2019 06/25/2019 CUSTOMER 32 06/23/2020 09/25/2020 11/11/2020 CUSTOMER 33 06/23/2020 09/25/2020 10/07/2020 CUSTOMER 34 04/09/2019 04/15/2019 CUSTOMER 35 04/09/2019 05/10/2019 CUSTOMER 36 04/09/2019 07/25/2019 CUSTOMER 37 04/09/2019 08/20/2020 10/22/2020 12/09/2020 09/23/2020 CUSTOMER 38 02/18/2019 06/28/2019 CUSTOMER 39 04/09/2019 05/11/2019 CUSTOMER 40 07/02/2019 08/15/2019 CUSTOMER 41 07/02/2019 CUSTOMER 42 06/23/2020 11/11/2020 CUSTOMER 43 04/09/2019 11/02/2019 CUSTOMER 44 04/09/2019 08/20/2020 10/22/2020 12/09/2020 CUSTOMER 45 08/20/2020 10/22/2020 12/09/2020 12/07/2020 CUSTOMER 46 07/02/2019 CUSTOMER 47 04/09/2019 11/22/2019 Thank you so much!

Eugenio211 · ‎01-07-2021

Hi - I'm trying to get the date difference (number of days) of the loan dates of each customer. The idea is to get the number of days: loan 1 - loan 2, then loan 1 - loan 3, and then loan 1 - loan 4. below is my example data: CUSTOMER LOAN # LOANDATE Date Diff Cust A 1 4-Jan-20 Cust A 2 11-Feb-20 Cust A 3 27-Mar-20 Cust A 4 4-Apr-20 Cust B 1 21-Jun-20 Cust B 2 18-Jul-20 Cust B 3 15-Aug-20 Cust B 4 9-Sep-20 Thank you so much!

Eugenio211 · ‎01-06-2021

Thank you for the quick response.

Eugenio211 · ‎01-06-2021

Hi - I just want to ask some help with regards to flagging Unique and Duplicates. below is my sample data, in excel I used '=IF(I2=I1,1,0)' how do we translate that to sas? thank you. CUSTOMER_KEY DidIntLoan Uniq Customer A 0 0 Customer B 0 0 Customer B 0 1 Customer B 0 1 Customer C 0 0 Customer C 0 1 Customer D 0 0

Eugenio211 · ‎12-22-2020

Hi - my apologies for the confusion and my bad for the wrong statement of the requirement. the requirement should be LoanDate ge campaign_date. as the idea is the transaction happened after the campaign date. again Thank you so much for the help.

Eugenio211 · ‎12-22-2020

Hi - the data set is already sorted by campaign_date in the data set prior to the query you have created. this is how the data is arranged and I used proc transpose to come up with this. CUSTOMER_KEY campaign_date1 campaign_date2 campaign_date3 campaign_date4 LOANDATE 80111608 09/19/2019 08/20/2020 10/22/2020 12/09/2020 12/08/2020 in the above example, the campaing_converted should be 10/22/2020, as the loan transaction happened after campaign_date3 and before campaing_date4, thank you so much.

Eugenio211 · ‎12-21-2020

Hi - Hope all is well. I was going through the records generated by the query you have created. I found out that some records are not working as expected. Like the example record from below where the loan date is less than the campaign converted. Instead of 12/09/2020 it should be 10/22/2020 as the loan date is 12/09/2020. CUSTOMER_KEY CAMPAIGN_DATE LOANDATE CAMPAIGN_CONVERTED 80111608 08/20/2020 80111608 12/09/2020 12/08/2020 12/09/2020 80111608 10/22/2020 80111608 09/19/2019 Thanks a lot!

Eugenio211 · ‎12-16-2020

Thank you so much!

Eugenio211 · ‎12-16-2020

Hello - Thank you for taking time on creating this. This actually is working. However I was trying to modify it as I wanted to show the actual campaign date in the 'campaign_converted' column. Current: CUSTOMER_KEY campaign_date1 campaign_date2 campaign_date3 campaign_date4 LOAN_KEY CASH_AMT LOAN_BALANCE LOANDATE PRODUCT_TYPE campaign_converted 80000048 04/09/2019 95853123 $6,770.00 $5,194.69 05/31/2019 O campaign_date1 80000051 07/02/2019 96138629 $3,637.00 $2,930.73 08/14/2019 O campaign_date1 80000062 09/19/2019 08/20/2020 10/22/2020 12/09/2020 80000084 04/09/2019 08/20/2020 10/22/2020 12/09/2020 80000094 06/23/2020 09/25/2020 11/11/2020 80000208 11/11/2020 80000217 07/02/2019 96149150 $7,860.00 $7,270.36 08/16/2019 O campaign_date1 Expected: CUSTOMER_KEY campaign_date1 campaign_date2 campaign_date3 campaign_date4 LOAN_KEY CASH_AMT LOAN_BALANCE LOANDATE PRODUCT_TYPE campaign_converted 80000048 04/09/2019 95853123 $6,770.00 $5,194.69 05/31/2019 O 04/09/2019 80000051 07/02/2019 96138629 $3,637.00 $2,930.73 08/14/2019 O 07/02/2019 80000062 09/19/2019 08/20/2020 10/22/2020 12/09/2020 80000084 04/09/2019 08/20/2020 10/22/2020 12/09/2020 80000094 06/23/2020 09/25/2020 11/11/2020 80000208 11/11/2020 80000217 07/02/2019 96149150 $7,860.00 $7,270.36 08/16/2019 O 07/02/2019

Eugenio211 · ‎12-14-2020

Thank you for the code. however it does not work as expected also the variable campaign_converted is not showing the campaign dates. if you look at row 1 from the result below, it says campaign_date4, however campaign_date1 is the only date available from that row. In addition, when loandate is blank it should not return a campaign_date. CUSTOMER_KEY campaign_date1 campaign_date2 campaign_date3 campaign_date4 LOAN_KEY CASH_AMT LOAN_BALANCE LOANDATE PRODUCT_TYPE campaign_converted 80000048 04/09/2019 95853123 $6,770.00 $5,181.87 05/31/2019 O campaign_date4 80000051 07/02/2019 96138629 $3,637.00 $2,923.40 08/14/2019 O campaign_date4 80000062 09/19/2019 08/20/2020 10/22/2020 12/09/2020 campaign_date2 80000084 04/09/2019 08/20/2020 10/22/2020 12/09/2020 campaign_date2 80000094 06/23/2020 09/25/2020 11/11/2020 campaign_date2

Eugenio211 · ‎12-14-2020

Thank you, this works as expected. This step will be updated every month when new campaign is added. and when more campaign months are added this data step will be long and messy to look at. is there a way to simplify this as to: when loandate is ( campaign_date1 campaign_date2 ...........) then CAMPAIGN_CONVERTED = campaign_date thanks a lot.

Eugenio211 · ‎12-11-2020

thank you, however it did not work as expected. i got all null.

Online Status	Offline
Date Last Visited	‎01-03-2024 05:04 PM

2024 Weekdates

if then do statements

Re: date extract

Re: date extract

date extract

Proc Transpose help

if else then do statement

SQL spell function

Re: Sending Email

Re: Sending Email

Re: Convert Character to date

Re: Convert Character to date

Re: if else if

Re: if else if

Re: if else if

Day Count

summary count using array

Daylight Savings Time

Proc Report

Re: Data Step / Query

Re: if else if

Re: Using array instead of 'if else...then do'

Re: Using array instead of 'if else...then do'

Using array instead of 'if else...then do'

Date Difference

Re: Flagging Unique and Duplicates

Flagging Unique and Duplicates

Re: if else if

Re: if else if

Re: if else if

Re: if else if

Re: if else if

Re: if else if

Re: if else if

Re: if else if