BookmarkSubscribeRSS Feed
JMolnar
Calcite | Level 5

We are on 6.3 Hotfix 0.1

 

What we are challenged with is performing segmentation in the tool and having it be output as an optional field as the tool doesn’t seem to allow us to use IF or CASE statement-like functionality

 

What I mean by this is

 

Let’s say the output of my list is

 

BAN       CTN       City        Device  ARPU    Segment1          Segment2

1             2             Mtl         Iphone 25           Cell4                      Key1

1             6             Tor         BB          200         Cell4                      Key2

3             7             Cgy        Samsg   30           Cell2                      Null

4             8             Van        Iphone 40           Cell2                      Key1

5             9             Hfx         Iphone 60           Cell3                      Key3

 

 

Rules are as follows

If sum(ARPU) per BAN <30 then segment1 = Cell1

If sum(ARPU) per BAN  <50 then segment1 = Cell2

If sum(ARPU) per BAN  <100 then segment1 = Cell3

If sum(ARPU) per BAN  <300 then segment1 = Cell4

 

If city in (Mtl,Tor,Van) and device = ‘Iphone’ Then segment2 = ‘Key1’

If device = ‘BB’ Then segment2 = ‘Key2’

If city = “Hfx’ then segment2 = ‘Key3’

 

Here is the real code we are trying to implement

 

 

/* keycode */

rsubmit;

proc sql;

               update svr.&Campaign_Name._ilp_07

               set   OPT1 = 'BAN:$' || strip(put(all_ban_mm_arpu, 10.2)) || ';' || 'CTN:$' || strip(put(all_mm_arpu_pre,10.2))

                              , OPT2 = case when NO_OF_LIVE_CTNS = 1 then 'SL' else 'ML - ' || strip(put(NO_OF_LIVE_CTNS,4.)) end 

                              , OPT3 = strip(put(all_bl_data_pct,8.)) || '% Usage'

                              , OPT4 = strip(OFFR_SEGMENT)

                              , OPT5 = strip(KEY_ATTRIBUTE)

 

                              , OPT6 = case

                                                                           when offr_Lead_Smartphone_price is null then ''

                                                                           when language = 'EN' then '$' || strip(put(offr_Lead_Smartphone_price, 8.))

                                                                           else strip(put(offr_Lead_Smartphone_price, 8.)) || '$' end

                              , OPT7 = case

                                                                           when offr_Lead_Smartphone_price is null then ''

                                                                           else strip(offr_Lead_Smartphone_GB_disp) end

                              , OPT8 = case

                                                                           when NO_OF_LIVE_CTNS > 1 then ''

                                                                           when Share_Type <> 'SHARE EVERYTHING' then ''

                                                                           when offr_Lead_Smartphone_price - msf <= 0 then ''

 

                                                                           when language = 'EN' and offr_Lead_Smartphone_price - msf > 0 then '$' || strip(put(abs(offr_Lead_Smartphone_price - msf),best8.2)) || ' more'

                                                                           when language = 'FR' and offr_Lead_Smartphone_price - msf > 1 then strip(put(abs(offr_Lead_Smartphone_price - msf),best8.2)) || '$ de plus par mois'

                                                            end

                              , OPT9 = ''

                              , OPT10 = case when all_ban_data_usage = 0 then 'VOICE ONLY' else 'VOICE & DATA' end

 

                              , WES  = Share_Type

                              , KEYCODE = 'TARGET'

                              , MKT_ACCOUNT_TYPE = case when has_data_ind = 1 then 'D' else 'V' end

 

               ;

quit;

endrsubmit;

 

 

 

rsubmit;

proc sql;

update svr.&Campaign_Name._ilp_08

set keycode =

               case

                              when all_ban_data_usage = 0 then 'VOICE'

                              when Share_Type <> 'SHARE EVERYTHING' and OFFR_LEAD_SOC is not null and all_bl_data_pct >= 90 then 'LEGDATA'

                              when Share_Type <> 'SHARE EVERYTHING' and OFFR_LEAD_SOC is not null and all_bl_data_pct >= 70 then 'LEGREG'

 

                              when Share_Type = 'SHARE EVERYTHING' and OFFR_LEAD_SOC is not null and SE_Plus_Flag_Lead = 1 and all_bl_data_pct >= 90 then 'SEDATA'

                              when Share_Type = 'SHARE EVERYTHING' and OFFR_LEAD_SOC is not null and SE_Plus_Flag_Lead = 1 and all_bl_data_pct >= 70 then 'SEREG'

 

                              when Share_Type <> 'SHARE EVERYTHING' then 'LEGALL'

                              when Share_Type = 'SHARE EVERYTHING' then 'SEALL'

 

                              else 'N/A'

               end

;

quit;

endrsubmit;

6 REPLIES 6
SteveMarshall
SAS Employee

CI studio supports CASE Statement in calculated fields.  The Calculated field can be defined in the Information Map or within the Definitions workspace in CI Studio as a Calculated Item.   The variables used in the CASE statement must be from the MA Information map or can be other Calculated items.

SteveMarshall
SAS Employee
Two Valid Case statements for Calculated Items in my CI environment:
Case when <</Customer/Status>> = 1 then 'Key1' when <</Customer/Status>> =2 then 'Key2' when <</Customer/Status>> = 3 then 'Key3' else 'N/A' end

or
Case <</Customer/Status>> when 1 then 'Key1' when 2 then 'Key2' when 3 then 'Key3' else 'N/A' end
SteveMarshall
SAS Employee
Once the Calculated Item is created, it can be used in Exports or Selections / Splits (by data Items).
RajaMarla
SAS Employee

One of the other resources available for you (besides the calculated field option), is to write your SAS code to perform any computation or derive a field of your choice.   If you can do it using Base SAS, you pretty much can do the same using a "process node" on the MA diagram.

 

Inside a process node, you can write your own data step code or PROC-SQL to access databases/SAS datasets etc.  You can also store intermediate results in a SAS dataset or a database and then retrieve/combine the results with other calculations in a second process node on the same or other diagrams.

 

Please explore the "process node" documentation (SAS MA user guide) for additional help or ask your questions on this forum.

pcapazzi
Pyrite | Level 9

I think the snippet of code regarding "keycode" can be implemented in CI using nodes. This is how I would try it:

 

1) Your first split would be based on all_ban_data_usage (0 in one cell, all others in another cell). The cell with 0 would go into the comm node.

2) Then split the 'all others' by OFFER_LEAD_SOC where missing values go in one cell and everything else in the other cell. The "everything else" goes into another cell by selecting all fields but renaming the output cell to be the same. So there should be an output name for "missing values" and one for "everything else".

3) The 'missing values' node would go into a split by Share_Type. Just like OFFER_LEAD_SOC that split node would need all values of the field to be selected. "SHARE EVERYTHING" would be it's own cell and retain it's name. All others would need their output cell names change to be something common like "NOT SHARE EVERYTHING" so you get only two nodes coming out of it. 

4)You would then take "non missing values" and split that by SE_Plus_Flag_Lead

5) Then you would split by all_bi_data_pct so that min 90 and max * goes to one cell and then min 70 and max 90 goes to another cell. 

 

There are other ways of segmenting data such as prioritze. For instance, instead of selecting all values of a field you can have a select statement of missing values in OFFER_LEAD_SOC point into a prioritize that also has your other data as another input. The Offer_Lead_Soc with missing values will come out on the other end with the same count but here you can use the Create A Cell For Remainder function to produce "all other values". 

 

If you have duplicate subjects (BANs?) in that table being updated make sure that you pay close attention to the priority order in these nodes where the preferred segment is on top. 

 

I think the other code that updates the various OPT# and other fields (1st half of the code provided) would need to be setup in a process node and/or calculated items. If the OPT and other fields are in the infomap then, as suggested before, a process node could update the table they reside in and be used by splits and prioritizes afterwards.

 

Hope that helps. 

 

GoExplore
Obsidian | Level 7

Solution provided by @SteveMarshall is simple and works perfectly well.

How to improve email deliverability

SAS' Peter Ansbacher shows you how to use the dashboard in SAS Customer Intelligence 360 for better results.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1746 views
  • 5 likes
  • 5 in conversation