Solved: Re: if else if

Eugenio211 · Posted 12-11-2020 03:34 PM

Hi,

I am trying to do an 'if, else if, then' clause to get a new variable named Campaign_Converted. However it seems that the logic that i am using is not working. below is the data step that I wrote. I've attached the data I am using.

the idea is to get the campaign date as campaign_converted where in the loan date is >= the campaign_date

data can_approvals3_t; set can_approvals2_t;
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;

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;

View solution in original post

PhilC · Posted 12-11-2020 03:57 PM

data can_approvals3_t; set can_approvals2_t;
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;

That's a crazy nested if then statement. I think You need to use do blocks like so:

if LoanDate >= campaign_date1 and campaign_date2 = . 
   and campaign_date3 = .
   and campaign_date4 = . 
then CAMPAIGN_CONVERTED = Campaign_date1;
else do;*<----------;
     if LoanDate >= campaign_date1 
        and LoanDate < campaign_date2
        and campaign_date3 = . and campaign_date4 = . 
     then CAMPAIGN_CONVERTED = Campaign_date1;
     else do;*<-------; 
          if LoanDate >= campaign_date1 
             and LoanDate < campaign_date2 
             and LoanDate < campaign_date3
             and LoanDate < campaign_date4 
          then CAMPAIGN_CONVERTED = Campaign_date1;
          else do;*<-------; 
*
.
.
 
                                   then CAMPAIGN_CONVERTED = Campaign_date4;
                        end;
                    end;
                end;
            end;
        end;
    end;
end;

run;

PhilC · Posted 12-11-2020 04:01 PM

You can use a select statement too.

data can_approvals3_t; set can_approvals2_t;
format CAMPAIGN_CONVERTED mmddyy.;

select;
  when (LoanDate >= campaign_date1 and campaign_date2 = . 
        and campaign_date3 = .
        and campaign_date4 = . )
     CAMPAIGN_CONVERTED = Campaign_date1;
  when (LoanDate >= campaign_date1 
        and LoanDate < campaign_date2
        and campaign_date3 = . and campaign_date4 = . )
     CAMPAIGN_CONVERTED = Campaign_date1;
  when (LoanDate >= campaign_date1 
         and LoanDate < campaign_date2 
         and LoanDate < campaign_date3
         and LoanDate < campaign_date4) 
     CAMPAIGN_CONVERTED = Campaign_date1;
*
.
. 
     CAMPAIGN_CONVERTED = Campaign_date4;
end;

run;

Eugenio211 · Posted 12-14-2020 11:44 AM

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.

Shmuel · Posted 12-14-2020 12:48 PM

Using array simplifies code. Next code is adapted to any number of camaigns:

%let last_cmpgn = 4; /* adapt number to max campaingns */
data want;
 set have;
      array cm {} campaign1-campaign&last_cmpgn;
      do i=1 to dim(cm);;
          if loan_date < cm(i) or i=&last_cmpgn then do; 
             if i = &last_cmpgn then j=i;
             else j = i +1;
              campaign_converted = vname(cm(j));
              leave;
           end;           
      end;      
     drop i j;
run;

Eugenio211 · Posted 12-14-2020 01:29 PM

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

Shmuel · Posted 12-14-2020 03:09 PM

Next code is tested.

I have focused on dates only and ignored other variables while creating the test data.

I have added a loandate to row 3 just for the test.

You can assign the campaign date to the converted date by dropping the VNAME function and assigning the value of the array member itself.

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;
80000048	04/09/2019	 	 	 	95853123	$6,770.00	$5,181.87	05/31/2019	O	
80000051	07/02/2019	 	 	 	96138629	$3,637.00	$2,923.40	08/14/2019	O	
80000062	09/19/2019	08/20/2020	10/22/2020	12/09/2020	 	 	 	10/30/2019 	O 	
80000084	04/09/2019	08/20/2020	10/22/2020	12/09/2020	 	 	 	 	 	
80000094	06/23/2020	09/25/2020	11/11/2020	 	 	 	 	 	 	
;
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=. or (j <= &last_cmpgn and cm(j)=.) or i=&last_cmpgn then j=i; 
         campaign_converted = vname(cm(j));  /* campaign_converted = cm(j);  */
         leave;
     end;      
     drop i j;
run;

Shmuel · Posted 12-14-2020 04:23 PM

Maybe next code is better, test on part of the xlsx file you have attached:

%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;
            campaign_converted = vname(cm(i)); leave; end;
         else 
         if cm(i) > loandate then do; campaign_converted = vname(cm(j)); leave; end;
     end;
     drop i j;
run;

In case it still does not fit the requirements, please post the right expected campaign.

Eugenio211 · Posted 12-16-2020 09:41 AM

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

Shmuel · Posted 12-16-2020 10:08 AM

Just change from assigning the variable name:

campaign_converted = vname(cm(j));

to assign the value of that variable:

 campaign_converted = cm(j);

Pay attention not to change the index (i or j) - use the original one.

Eugenio211 · Posted 12-16-2020 10:13 AM

Thank you so much!

Eugenio211 · Posted 12-21-2020 02:53 PM

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!

Shmuel · Posted 12-21-2020 04:12 PM

It the first time the company_key spreads on more than one observation.

You need create the new dataset with a sorted array of campaign_date:

09/19/2019 08/20/2020 12/09/2020 10/22/2020 loan_date = 12/08/2020

and campaign_convert is 12/09/2020 > 12/08/2020 as desired.

If you insist that this is not the expected date, please explain why.

Eugenio211 · Posted 12-22-2020 08:22 AM

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.

Shmuel · Posted 12-22-2020 11:30 AM

1) Pay attention that you are using a date format mmddyy10.

2) You asked in one of your previous posts:

"the requirement is to find the campaign date GE loan date".

In this case loan date id DEC 08, 2020 and the campaign date that the code

found is DEC 09, 2020 - just one day after the loan date..

3) You should be right if the requirement would be the next MONTH or later.

Please check yourself again.

SAS Innovate 2025: Call for Content

Classroom Training Available!