topic Re: if else if in SAS Programming

if else if

Eugenio211 — Fri, 11 Dec 2020 20:34:16 GMT

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;

Re: if else if

PhilC — Fri, 11 Dec 2020 20:57:38 GMT

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;

Re: if else if

PhilC — Fri, 11 Dec 2020 21:05:56 GMT

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;

Re: if else if

mkeintz — Fri, 11 Dec 2020 21:03:15 GMT

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.

Re: if else if

Shmuel — Fri, 11 Dec 2020 21:28:52 GMT

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;

Re: if else if

PGStats — Fri, 11 Dec 2020 22:09:19 GMT

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)

Re: if else if

Eugenio211 — Fri, 11 Dec 2020 22:29:01 GMT

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

Re: if else if

Eugenio211 — Fri, 11 Dec 2020 22:29:51 GMT

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

Re: if else if

PGStats — Sat, 12 Dec 2020 01:15:37 GMT

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

Re: if else if

Shmuel — Sat, 12 Dec 2020 07:05:13 GMT

@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.

Re: if else if

Eugenio211 — Mon, 14 Dec 2020 16:44:43 GMT

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.

Re: if else if

Shmuel — Mon, 14 Dec 2020 17:51:40 GMT

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;

Re: if else if

Eugenio211 — Mon, 14 Dec 2020 18:29:10 GMT

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

Re: if else if

Shmuel — Mon, 14 Dec 2020 20:09:31 GMT

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;

Re: if else if

Shmuel — Mon, 14 Dec 2020 21:23:59 GMT

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.

Re: if else if

Eugenio211 — Wed, 16 Dec 2020 14:41:33 GMT

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

Re: if else if

Shmuel — Wed, 16 Dec 2020 15:11:09 GMT

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.

Re: if else if

Eugenio211 — Wed, 16 Dec 2020 15:13:03 GMT

Thank you so much!

Re: if else if

Eugenio211 — Mon, 21 Dec 2020 19:53:54 GMT

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!

Re: if else if

Shmuel — Mon, 21 Dec 2020 21:12:34 GMT

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.