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;
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.
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.
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.
Solution provided by @SteveMarshall is simple and works perfectly well.
Want to review SAS CI360? G2 is offering a gift card or charitable donation for each accepted review. Use this link to opt out of receiving anything of value for your review.
Listen to the Reimagine Marketing podcast
Assess your marketing efforts with a free tool
SAS Customer Intelligence Learning Subscription (login required)
Compatibility notice re: SAS 9.4M8 (TS1M8) or later
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.
Want to review SAS CI360? G2 is offering a gift card or charitable donation for each accepted review. Use this link to opt out of receiving anything of value for your review.
Listen to the Reimagine Marketing podcast
Assess your marketing efforts with a free tool
SAS Customer Intelligence Learning Subscription (login required)
Compatibility notice re: SAS 9.4M8 (TS1M8) or later