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!
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;
This is a dangerous solution, since I haven't had a morning cup of coffee. See if this does what you need:
if loan_date >= campaign_date4 > . then CAMPAIGN_CONVERTED=campaign_date4;
else if loan_date >= campaign_date3 > . then CAMPAIGN_CONVERTED=campaign_date3;
else if loan_date >= campaign_date2 > . then CAMPAIGN_CONVERTED=campaign_date2;
else if loan_date >= campaign_date1 > . then CAMPAIGN_CONVERTED=campaign_date1;
If this gets what you want (for the current set of variables) it would be easy to convert this logic to use an array.
Thanks for the response, but this is what I am currently using. I am trying to see if this can be done using 'array'.
Truthfully, this logic doesn't look anything like the logic you are currently using. Your logic needed to be simplified to make it possible for arrays to be used.
If this logic looks good to you, here's a way to expand it to add arrays. The logic requires that the campaigns have been defined in order (meaning that campaign_date1 was defined before campaign_date2, which was defined before campaign_date3, etc.):
array campaigns {*} campaign_date: ;
do _n_=dim(campaigns) to 1 by -1 until (CAMPAIGN_CONVERTED > .);
if loan_date >= campaign_date{_n_} > . then CAMPAIGN_CONVERTED=campaign_date{_n_};
end;
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: