topic Re: Using array instead of 'if else...then do' in SAS Programming

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

Eugenio211 — Mon, 11 Jan 2021 16:39:30 GMT

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!

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

Eugenio211 — Mon, 11 Jan 2021 16:53:20 GMT

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;

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

Astounding — Mon, 11 Jan 2021 17:08:49 GMT

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.

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

Eugenio211 — Mon, 11 Jan 2021 17:08:25 GMT

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

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

Astounding — Mon, 11 Jan 2021 17:16:05 GMT

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;