Solved: Re: if else if - Page 2

Eugenio211 · Posted 12-22-2020 02:19 PM

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.

Shmuel · Posted 12-22-2020 03:35 PM

It is more logic to require loandate >= campaign_date or more precisely as in my first post:

"Your requirement is to find the last campaign prior the loan_date.".

It easy to fit the code to that requirement:

%let last_cmpgn = 4; /* adapt number to max campaingns */
data want;
 set have;
      array cm {*} campaign_date1-campaign_date&last_cmpgn;
      do i=1 to dim(cm);
         j = i+1;
         if loandate=. then leave; else 
         if (j <= &last_cmpgn and cm(j)=.) or i=&last_cmpgn then do;
		    if i>1 then campaign_converted = cm(i-1);
			else campaign_converted = cm(i); /* or missing ??? */
            leave; 
		 end;
         else 
         if cm(j) > loandate then do; 
		    campaign_converted = cm(i); 
			leave; 
		 end;
     end;
     drop i j;
run;

mkeintz · Posted 12-11-2020 04:03 PM

Please provide your sample data in the form of a working data step. You will benefit more if we invest our time in helping you diagnose the problem rather than in making a working sas data set from your excel sheet - which some people won't download for security reasons.

Also show us what you got, contrasted to what you expected. Otherwise we are examining a problem statement that is not far enough above the all-too-common "doesn't work" characterization.

Help us help you.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

Eugenio211 · Posted 01-11-2021 01:40 PM

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

Shmuel · Posted 01-12-2021 12:28 AM

Check next code:

data have;
   informat campaign_date1-campaign_date4 loandate mmddyy10.;
   format   campaign_date1-campaign_date4 loandate mmddyy10.;
   infile cards truncover dlm='09'x dsd;
   input CUSTOMER_KEY	
         campaign_date1	
		 campaign_date2	
		 campaign_date3	
		 campaign_date4	
/* 		 LOAN_KEY	 */
/* 		 CASH_AMT	 */
/* 		 LOAN_BALANCE	 */
		 loandate  	
/* 		 PRODUCT_TYPE	 */
		;
cards;
1	04/09/2019	 	 	 	05/31/2019	 
2	07/02/2019	 	 	 	08/14/2019	 
3	09/19/2019	08/20/2020	10/22/2020	12/09/2020	 	 
4	04/09/2019	08/20/2020	10/22/2020	12/09/2020	 	 
5	06/23/2020	09/25/2020	11/11/2020	 	 	 
6	11/11/2020	 	 	 	 	 
7	07/02/2019	 	 	 	08/16/2019	 
8	07/17/2020	10/22/2020	12/09/2020	 	 	 
9	04/09/2019	 	 	 	07/05/2019	 
10	04/09/2019	 	 	 	05/24/2019	 
11	06/23/2020	 	 	 	 	 
12	06/23/2020	09/25/2020	11/11/2020	 	 	 
13	04/09/2019	08/20/2020	12/09/2020	 	 	 
14	04/09/2019	 	 	 	04/11/2019	 
15	07/17/2020	10/22/2020	 	 	 	 
16	04/09/2019	 	 	 	12/23/2019	 
17	04/09/2019	 	 	 	05/18/2019	 
18	04/09/2019	 	 	 	05/28/2019	 
19	04/09/2019	 	 	 	04/29/2019	 
20	10/22/2020	12/09/2020	 	 	 	 
21	12/09/2020	 	 	 	12/30/2020	 
22	04/09/2019	 	 	 	07/10/2019	 
23	04/09/2019	 	 	 	04/13/2019	 
24	04/09/2019	 	 	 	04/20/2019	 
25	04/09/2019	 	 	 	08/08/2020	 
26	04/09/2019	 	 	 	08/30/2019	 
27	04/09/2019	 	 	 	12/22/2019	 
28	07/02/2019	 	 	 	08/06/2019	 
29	04/09/2019	 	 	 	02/04/2020	 
30	12/09/2020	 	 	 	 	 
31	04/09/2019	 	 	 	06/25/2019	 
32	06/23/2020	09/25/2020	11/11/2020	 	 	 
33	06/23/2020	09/25/2020	 	 	10/07/2020	 
34	04/09/2019	 	 	 	04/15/2019	 
35	04/09/2019	 	 	 	05/10/2019	 
36	04/09/2019	 	 	 	07/25/2019	 
37	04/09/2019	08/20/2020	10/22/2020	12/09/2020	 	 
38	02/18/2019	 	 	 	06/28/2019	 
39	04/09/2019	 	 	 	05/11/2019	 
40	07/02/2019	 	 	 	08/15/2019	 
41	07/02/2019	 	 	 	 	 
42	06/23/2020	11/11/2020	 	 	 	 
43	04/09/2019	 	 	 	11/02/2019	 
44	04/09/2019	08/20/2020	10/22/2020	12/09/2020	 	 
45	08/20/2020	10/22/2020	12/09/2020	 	12/07/2020	 
46	07/02/2019	 	 	 	 	 
47	04/09/2019	 	 	 	11/22/2019	  	 	 	 	 	 	
;
run;

%let last_cmpgn = 4; /* adapt number to max campaingns */
data want;
 set have;
      array cm {*} campaign_date1-campaign_date&last_cmpgn;
      do i=1 to dim(cm);
         j = i+1;
         if loandate=. then leave; else 
         if (j <= &last_cmpgn and cm(j)=.) or i=&last_cmpgn then do;
		    if i>1 then campaign_converted = cm(i-1);
			else campaign_converted = cm(i); /* or missing ??? */
            leave; 
		 end;
         else 
         if cm(j) > loandate then do; 
		    campaign_converted = cm(i); 
			leave; 
		 end;
     end;
     drop i j;
     FORMAT campaign_converted MMDDYY10.;
run;

In case of any issue please explain it and post the right campaign_converted expected.

Eugenio211 · Posted 01-12-2021 09:07 AM

Hello - thanks always for your help. I ran your code and it did not work as expected. I've got rows that did not meet the objective.

see example: the code picked up the wrong campaign_date(n), campaign_converted should be campaign_date3.

CUSTOMER	campaign_date1	campaign_date2	campaign_date3	campaign_date4	LOANDATE	CAMPAIGN_CONVERTED
CUSTOMER A	06/23/2020	09/25/2020	11/11/2020		11/28/2020	09/25/2020

Shmuel · Posted 01-12-2021 10:13 AM

Bug is fixed:

%let last_cmpgn = 4; /* adapt number to max campaingns */
data want1;
 set have;
      array cm {*} campaign_date1-campaign_date&last_cmpgn;
      do i=1 to dim(cm);
         j = i+1;
         if loandate=. then leave; else 
         if (j <= &last_cmpgn and cm(j)=.) or i=&last_cmpgn then do;
		    if i>1 then campaign_converted = cm(j-1);   /* <<< line changed */
			else campaign_converted = cm(i); /* or missing ??? */
            leave; 
		 end;
         else 
         if cm(j) > loandate then do; 
		    campaign_converted = cm(i); 
			leave; 
		 end;
     end;
     drop i j;
     FORMAT campaign_converted MMDDYY10.;
run;

Eugenio211 · Posted 01-12-2021 10:35 AM

this works perfect. the data lined up with the expectation. Thank you so much.
One last favor, would it be possible for you to add a comment as to what each step do? I want to understand more each steps function.
from 'array' step up to the last 'if' statement.

Thanks a lot!

Shmuel · Posted 01-12-2021 11:16 AM

You better read the sas documentation of using arrays.

Search by google for "sas documentation array".

Anyhow I shall try to give you some hints:

1) VAR1-VAR4 is a short code for the list VAR1 VAR2 VAR3 VAR4.

2) By defining an array like:

array cm {*} campaign_date1-campaign_date&last_cmpgn;

it enables to point easily to any member of the list, thus:

cm(1) is equivalent to campaign1 - the first member in the list.

cm(2) is equivalent to campaign2 - the first member in the list. etc.

3) Arrays' member is easily pointed by an any numeric variable like i, j

thus enable pointing forwards or backwards by adding/subtracting its value.

4) I suggest you to add some PUTLOG statements in the code to get the values

at run time of specific variables and learn why code worked as it worked.

I'm not sure my English is good enough to explain better.

Shmuel · Posted 12-11-2020 04:26 PM

If I understand correctly there are 4 campaigns named vampaign1-campagin4 and named chronologically. Your requirement is to find the last campaign prior the loan_date.

data want;
 set have;
      array cm {} campaign1-campaign4;
      do i=1 to 4;
          if loan_date < cm(i) then do;
              j = max(1, i-1);
              campaign_converted = vname(cm(j));
               leave;
           end;
           else if i=4 then campaign_converted = vname(cm(i));
      end;
      drop i j;
run;

Eugenio211 · Posted 12-11-2020 05:29 PM

thank you, the requirement is the to find the campaign date ge the loan date.

Shmuel · Posted 12-12-2020 01:59 AM

@Eugenio211 wrote:
thank you, the requirement is the to find the campaign date ge the loan date.

In this case next code will find the first campaign date > loan_date:
data want;
 set have;
      array cm {} campaign1-campaign4;
      do i=1 to 4;
          if loan_date < cm(i) or i=4 then do; 
              campaign_converted = vname(cm(j));
              leave;
           end;           
      end;      
     drop i;
run; 

Pay attention - in case there is no campaign date >= loan date you will get campaign4.

PGStats · Posted 12-11-2020 05:09 PM

Try this:

data can_approvals3_t; 
set can_approvals2_t;
format CAMPAIGN_CONVERTED mmddyy.;
array cd campaign_date1 - campaign_date4;
do i = 4 to 1 by -1 until (CAMPAIGN_CONVERTED);
	if load_date >= coalesce(cd{i}, constant("BIG")) then 
		CAMPAIGN_CONVERTED = cd{i};
	end;
drop i;
run;

(untested)

PG

Eugenio211 · Posted 12-11-2020 05:29 PM

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

PGStats · Posted 12-11-2020 08:15 PM

Sorry about that. Please provide usable data for testing (not Excel, not too large)

PG

The 2025 SAS Hackathon has begun!

SAS Training: Just a Click Away