Sum variables from column to column using --

[t30]

I have a list of customer records.

For each customer, we create a binary variable to see whether they have taken up an offer for any particular month through a campaign period. Say, campaign period runs from July until October. If the customer took an offer in August, the flag equals to 1, 0 otherwise. The customer may take an offer in other months as well during that period.

Now, for each customer, I'm interested to know what were their spending prior and post the first time they took the offer.

For example, customer id_01 took the offer for the first time in July. Therefore the spend one month before is the spend in June.

For customer id_02, the first offer was in the month of August. Therefore the spend one month before for this customer is the spend in July.

Basically for each customer, I want to track their spend prior to the first offer take up. Same logic can be used to get the spending post the offer take up.

Thanks

 data have;
   input cust_id:$8. 
   cust_take_offer_Jul:$1. 
   cust_take_offer_Aug:$1.
   cust_take_offer_Sep:$1.
   cust_take_offer_Oct:$1.
   cust_spend_Jan:10. 
   cust_spend_Feb:10. 
   cust_spend_Mar:10. 
   cust_spend_Apr:10. 
   /*the rest of the month until December*/
   cust_spend_Dec:10. 
 datalines; 
 id_01 1 0 1 0 69 112.5 ... 
 id_02 0 1 1 1 56.5 84 ... 
 id_03 0 0 1 1 65.3 98 ... 
 id_04 0 1 1 0 62.8 102.5 ... 
 id_05 0 0 1 0 63.5 102.5 ... 
 ;;;;



data want;
   input cust_id:$8. 
   first_offer_takeup:$1. 
   pre_spend_1month :10. 
   pre_spend_2month :10. 
   pre_spend_3month :10. 
   spend_takeup_offer_month : 10
   post_spend_1month :10. 
   post_spend_2month :10. 
   post_spend_3month :10.
 datalines; 
 id_01 July 69 112.5 ... 
 id_02 August 56.5 84 ... 
 id_03 September 65.3 98 ... 
 id_04 August 62.8 102.5 ... 
 id_05 September 63.5 102.5 ... 
 ;;;;

[gamotte]

Hello,

Pleas provide some test data in the form of a datastep and an example of the wanted results.

[t30]

I've updated the post, hope it clarifies. thanks for your help 🙂

[ballardw]

---

@t30 wrote:

I have a list of customer records.

Is there any simple function that allows me to anchor the creation of the new variable based on the baseline?

Thanks

---

Simple function, likely not.

Details: WHAT does your current data look like?

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.  Only include variables need to demonstrate the issues in your question.

Second, do you want a report, something people read, or a data set at the end of the process. If a report you may want to show what the result would look like.

Are your current "dates" actually SAS date values, numeric with a SAS format assigned such as DATE9, MMDDYY or similar, character or some numeric that impersonates a date such as 20190605? SAS provides a large number of tools to manipulate and use dates but we need to know which you have to start with.

[t30]

I think I exaggerate a bit on the simple function part. Otherwise, I just need the Want table, nothing fancy like reporting or anything.

As for your question on the date, I'm limiting myself on monthly interval. I'm using YYYYMM to capture this.

[t30]

My current solution is by using the IF THEN DO statement, it's quite verbose so thought that I may as well get some help from the experts 🙂

[ScottBass]

I've reformatted your code so it actually works, i.e. spaces between missing placeholders (.):

data have;
   input 
      cust_id:$8.    
      cust_take_offer_Jul:$1.    
      cust_take_offer_Aug:$1.   
      cust_take_offer_Sep:$1.   
      cust_take_offer_Oct:$1.   
      cust_spend_Jan:10.    
      cust_spend_Feb:10.    
      cust_spend_Mar:10.    
      cust_spend_Apr:10.
      cust_spend_Dec:10.
   ;
   datalines4;
id_01 1 0 1 0 69 112.5 . . . 
id_02 0 1 1 1 56.5 84 . . . 
id_03 0 0 1 1 65.3 98 . . . 
id_04 0 1 1 0 62.8 102.5 . . . 
id_05 0 0 1 0 63.5 102.5 . . . 
;;;;
run;

data want;
   input 
      cust_id:$8.
      first_offer_takeup:$10.
      pre_spend_1month :10.
      pre_spend_2month :10.
      pre_spend_3month :10.
      spend_takeup_offer_month :10   
      post_spend_1month :10.    
      post_spend_2month :10.    
      post_spend_3month :10. 
   ;
   datalines4;
id_01 July 69 112.5 . . . 
id_02 August 56.5 84 . . . 
id_03 September 65.3 98 . . . 
id_04 August 62.8 102.5 . . . 
id_05 September 63.5 102.5 . . . 
;;;;
run;

I don't know what you're trying to do re: pre_spend and post_spend?  Are you just copying the Jan to pre_spend_1month and post_spend_1month?  Or is this pre_spend_<first>month and post_spend_<first>month?

In general I don't like metadata to be contained in column names.  Future column renames could break your code.  But since that's the have data you've provided, perhaps this will get you started:

data test;
   set have;
   length 
      first_offer_takeup $10
      pre_spend_1month
      pre_spend_2month
      pre_spend_3month 8 
      spend_takeup_offer_month 8
      post_spend_1month
      post_spend_2month
      post_spend_3month 8
   ;
   array month{*} cust_take_offer:;
   array cust_spend{*} cust_spend:; 
   array pre_spend{*} pre_spend:;
   array post_spend{*} post_spend:;

   length buffer $100;

   do i=1 to dim(month);
      if month{i}=1 then do;
         buffer=vname(month{i});
         if missing(first_offer_takeup) then
            first_offer_takeup=scan(buffer,4,'_');  
         * I leave it to you to convert to the full month name ;
         * Either find a SAS format or create a user format to convert mon3 to mon_fullname ;
         first_month=i;
      end;
      * now fiddle with the index (first_month) to copy spend{} to pre and post ;
   end;

   drop cust_spend:;
run;

You should be able to fiddle with the array index (i or first_month) to copy the correct column data from have to want.

Hope this gets you started, otherwise ask a better question (i.e. your exact logic for pre and post_spend).

[t30]

Thanks Scott.

The objective is, I want to see whether by taking up an offer, customer's spending has increase or decrease. But since customer may take up an offer in different month, so that's why for each customer I need to know the spend before and after.