In SAS Customer Experience Analytics, a column in the temporary tables can be created, incorrectly, with a length of only 260 characters. While the length should not have a maximum value, this is the current behavior in the software. The current character length causes an insert-data failure when the data length is more than 260 characters.

To resolve this problem, click the Downloads tab in this note and copy the SAS code on that tab to the sasbatch.sh file. Then, in the sasbatch.sh file, locate EXIT=1 (a SAS warning code) and change it to EXIT=0.

Operating System and Release Information

%macro cxa_avae_pull_srch_fac(
  sessionAttribution=
 ,sessionData=
 ,visitDetailsData=
 ,detailDetailsData=
 ,formDetailData=
 ,searchResultsData=
 ,outputSrchStarFact=
)/*/STORE*/;
 
  %global load_retvar;
  %let load_retvar=;
 
  %local searchFormJoin searchDetailJoin;
  %let searchFormJoin=&submart_dbms_libname..avae_temp_searchFormJoin;
  %let searchDetailJoin=&submart_dbms_libname..avae_temp_searchDetailJoin;
  %if %sysfunc(exist(&searchFormJoin)) %then %do;
    proc sql noprint;
    drop table &searchFormJoin;
    quit;
    %if %cxa_avae_check_err() %then %goto badEnd;
  %end;
  %if %sysfunc(exist(&searchDetailJoin)) %then %do;
    proc sql noprint;
    drop table &searchDetailJoin;
    quit;
    %if %cxa_avae_check_err() %then %goto badEnd;
  %end;
 
  proc sql noprint;
  create table &searchFormJoin
  as select
    s.session_sk
   ,s.session_attribution_sk
   ,s.detail_sk
   ,s.search_nm
   ,s.search_results_dttm
   ,s.search_results_sk
   ,case
     when search_results_displayed=-1 or results_displayed_flg='0' then 1
     else 0
    end as search_unknown_results
   ,case
     when search_results_displayed=0 and results_displayed_flg='1' then 1
     else 0
    end as search_no_results_returned
   ,case
     when search_results_displayed>0 and results_displayed_flg='1' then 1
     else 0
    end as search_returned_results
   ,f.form_field_value
   ,form_field_sk
   ,form_field_detail_dttm
  from &searchResultsData s
  left join
       &formDetailData f
  on s.search_results_sk=f.search_results_sk;
  quit;
  %if %cxa_avae_check_err() %then %goto badEnd;
 
  /*To get the sort order right, we need to sort this in SAS*/
  options sortpgm=sas;
  proc sort data=&searchFormJoin(dbsastype=(detail_sk='char(32)' session_sk='char(29)' session_attribution_sk='char(32)'))
            out=work.searchFormJoin(rename=(detail_sk=detail_id session_sk=session_id session_attribution_sk=visit_id)
                                    drop=form_field_sk form_field_detail_dttm);
  by session_attribution_sk search_results_sk form_field_sk form_field_detail_dttm;
  run;
  %if %cxa_avae_check_err() %then %goto badEnd;
 
  proc sql noprint;
  drop table &searchFormJoin;
  quit;
  %if %cxa_avae_check_err() %then %goto badEnd;
 
  %local formJoinData;
  %let formJoinData=&submart_dbms_libname..avae_temp_searchFormJoinColaps;
 
  /*Delete target temp table if it exists.*/
  %if %sysfunc(exist(&formJoinData)) %then %do;
    proc sql noprint;
    drop table &formJoinData;
    quit;
    %if %cxa_avae_check_err() %then %goto badEnd;
  %end;
 
  %local connectStringNm;
  %let connectStringNm=&submart_dbms_libname.ConnectToString;
  %local cxawhSchemaNm;
  %let cxawhSchemaNm=&&&submart_cxa_source_libname.SchemaName;
 
  %local varCharWord numWord;
  %if &submart_dbms_engine_type=ORACLE %then %do;
    %let numWord=NUMBER;
    %let varCharWord=VARCHAR2;
  %end;
  %else %if &submart_dbms_engine_type=POSTGRES %then %do;
    %let numWord=NUMERIC;
    %let varCharWord=VARCHAR;
  %end;
 
  /*Create empty temp output table*/
  proc sql noprint;
  &&&connectStringNm;
  execute(
    create table &cxawhSchemaNm..%scan(&formJoinData,2,.) (
   	"session_attribution_sk" &numWord(32) NOT NULL,
    	"session_sk" &numword(29)   NOT NULL ,
    	"detail_sk" &numword(32)    NOT NULL ,
    	"internal_search_term" &varCharWord(4000),
    	"search_nm" &varcharword(42),
    	"search_no_results_returned" &numWord(8),
    	"search_results_dttm"       TIMESTAMP (6),
    	"search_results_sk" &numword(14),
    	"search_returned_results" &numWord(8),
    	"search_unknown_results" &numWord(8)
    )
    ) by %scan(&&&connectStringNm,3);
  quit;
  %if %cxa_avae_check_err() %then %goto badEnd;
 
  /*Create work version for later append.*/
  data work.formJoinData;
  set work.searchFormJoin;
  by visit_id search_results_sk;
  length internal_search_term $260;
  retain internal_search_term;
  if first.search_results_sk then internal_search_term=kstrip(form_field_value);
  else internal_search_term=kstrip(internal_search_term)||' / '||kstrip(form_field_value);
  if last.search_results_sk;
  internal_search_term=kpropdata(internal_search_term,'TRIM');/*Needed when UTF-8*//*keadam S1094828 20140624*/
  drop form_field_value;
  run;
  %if %cxa_avae_check_err() %then %goto badEnd;
 
  proc append base=&formJoinData(dbsastype=(detail_sk='char(32)' session_sk='char(29)' session_attribution_sk='char(32)'))
	      data=work.formJoinData(rename=(detail_id=detail_sk visit_id=session_attribution_sk session_id=session_sk)) force;
  run;
  %if %cxa_avae_check_err() %then %goto badEnd;
 
  %cxa_avae_index_dbms_table(
    &formJoinData
   ,session_attribution_sk search_results_sk
   ,forceLowerCase=Y)
  %if &index_retvar ne OK %then %goto badEnd;
 
  /*Need to find out some ordering details in terms of complete visits.
    Bring in data from detail_fact, by detail_sk, so we get all the pages,
    some of which will have search info from the search results.*/
  proc sql noprint;
  create table &searchDetailJoin as
  select d.session_attribution_sk
        ,s.search_results_sk
        ,d.detail_sk
  from &detailDetailsData d
  left join
       &formJoinData s
  on d.detail_sk=s.detail_sk
  where d.session_sk in (select distinct session_sk
                         from &searchResultsData);
  quit;
  %if %cxa_avae_check_err() %then %goto badEnd;
 
  /*Need to force ordering by SAS in case sort order from db is not the same*/
  proc sort data=&searchDetailJoin(dbsastype=(session_attribution_sk='char(32)' detail_sk='char(32)'))
            out=work.searchDetailJoin(rename=(session_attribution_sk=visit_id) drop=detail_sk);
  by session_attribution_sk detail_sk;
  run;
  %if %cxa_avae_check_err() %then %goto badEnd;
 
  proc sql noprint;
  drop table &searchDetailJoin;
  quit;
  %if %cxa_avae_check_err() %then %goto badEnd;
 
  /*Data will now look like this, ordered by visit and detail_sk (i.e. page order)
  search_results_sk   	
  .
  .
  1
  .
  .
  .
  2
  .
 
  In example above, pages remaining for the "2" search would be 3 (i.e. until next search or exit)
    (You don't count the page after the search, since that is the search results,
     but you do count the page for the next search.)
  Pages remaining after "1" search is 0.
 
  We also count repeats, which means how many searches ahead of this one in the visit
  (e.g. refined their searched or searched for something else). In this case,
  the value is 1 for "1", 0 for "2".*/
 
  /*Here we remove any pages at the beginning of a visit before any searches, we count the total
    number of searches in the visit, and propagate search_results_sk to each line following
    which does not have a search_results_sk, so that we can use this to count the pages, later.*/
  data work.searchSequencing(
        keep=currentSK visit_id
        rename=(currentSK=search_results_sk) index=(myindex=(visit_id search_results_sk)))
       work.searchCounts(keep=visit_id countTotSearches index=(visit_id));
  set searchDetailJoin;
  by visit_id;
  retain countTotSearches currentSK 0;
  if first.visit_id then do;
    currentSK=.;
    countTotSearches=0;
  end;
  if search_results_sk ne . then do;
    countTotSearches=countTotSearches+1;
	currentSK=search_results_sk;
  end;
  if currentSK ne . then output work.searchSequencing;
  if last.visit_id then output work.searchCounts;
  run;
  %if %cxa_avae_check_err() %then %goto badEnd;
 
  /*Now collapse to one record per visit per search, with the count
    of pages after the search, and the number of searches remaining.*/
  %local searchJoinData;
  %let searchJoinData=&submart_dbms_libname..avae_temp_searchJoinData;
 
  /*Delete target temp table if it exists.*/
  %if %sysfunc(exist(&searchJoinData)) %then %do;
    proc sql noprint;
    drop table &searchJoinData;
    quit;
    %if %cxa_avae_check_err() %then %goto badEnd;
  %end;
 
  /*Create WORK table to load to temp table in DB*/
  data work.searchJoinData;
  set work.searchSequencing;
  by visit_id search_results_sk;
  retain num_pages_viewed_afterwards num_additional_searches;
  if first.visit_id then do;
    set work.searchCounts key=visit_id;
    num_additional_searches=countTotSearches;
  end;
  if first.search_results_sk then do;
    num_pages_viewed_afterwards=0;
  end;
  num_pages_viewed_afterwards=num_pages_viewed_afterwards+1;
  if last.search_results_sk then do;
    num_additional_searches=num_additional_searches-1;
    /*Adjust pages given we do not count page after search, and that we are
      over-counting since we always have counted the search page as a page.
 
      This means that if the page count is 2 or less, we actually have
      no pages after this.
 
      If there are additional searches after this one, then we just take
      off the 1 page since we need to add 1 page at the end for the first
      page of the next search.
 
      If there are no additional searches, then we need to subtract 2,
      one for the actual search page, and 1 for the page after the search
      page (the search results page).*/
    if num_pages_viewed_afterwards<=2 then num_pages_viewed_afterwards=0;
    else if num_additional_searches>0 then num_pages_viewed_afterwards=num_pages_viewed_afterwards-1;
    else num_pages_viewed_afterwards=num_pages_viewed_afterwards-2;
    output;
  end;
  drop countTotSearches;
  run;
  %if %cxa_avae_check_err() %then %goto badEnd;
 
  /*Create empty temp output table*/
  proc sql noprint;
  &&&connectStringNm;
  execute(
    create table &cxawhSchemaNm..%scan(&searchJoinData,2,.) (
   	"session_attribution_sk" &numWord(32) NOT NULL,
    	"search_results_sk" &numWord(14),
    	"num_additional_searches" &numWord(8),
	    "num_pages_viewed_afterwards" &numWord(8)
    )
    ) by %scan(&&&connectStringNm,3);
  quit;
  %if %cxa_avae_check_err() %then %goto badEnd;
 
	proc append base=&searchJoinData(dbsastype=(session_attribution_sk='char(32)'))
	     data=work.searchJoinData(rename=(visit_id=session_attribution_sk)) force;
	run;
  %if %cxa_avae_check_err() %then %goto badEnd;
 
  %cxa_avae_index_dbms_table(
    &searchJoinData
   ,session_attribution_sk search_results_sk,forceLowerCase=Y)
  %if &index_retvar ne OK %then %goto badEnd;
 
  /*Now we are ready to bring everything back together to create the fact table.*/
  /*Get list of columns to select from session data*/
  %local sessionDataCont;
  proc contents data=&sessionData noprint out=work.cont(
    /*ORIGINATION_SK not needed from session data, since there is a dimension
      for visitor origination which gets joined by VISITOR_ID. This column
      comes instead, for Visit, from session_attribution data.*/
    keep=name where=(upcase(name) not in ("ORIGINATION_SK","SESSION_START_DTTM","SESSION_COMPLETE_LOAD_DTTM","NEW_VISITORS","RETURN_VISITORS")));
  run;
  %if %cxa_avae_check_err() %then %goto badEnd;
  proc sql noprint;
  select "s."||strip(lowcase(name)) into: sessionDataCont separated by ","
  from work.cont;
  quit;
  %if %cxa_avae_check_err() %then %goto badEnd;
 
  /*Get list of columns to select from attributions data*/
  %local sessionAttrCont;
  proc contents data=&sessionAttribution noprint out=work.cont(
    keep=name where=(upcase(name) not in ("SESSION_SK","SESSION_ATTRIBUTION_SK","SESSION_ATTRIBUTION_DTTM","SESSION_COMPLETE_LOAD_DTTM","DESTINATION_PAGE_SK")));
  run;
  %if %cxa_avae_check_err() %then %goto badEnd;
  proc sql noprint;
  select "a."||strip(lowcase(name)) into: sessionAttrCont separated by ","
  from work.cont;
  quit;
  %if %cxa_avae_check_err() %then %goto badEnd;
 
  /*Get list of columns to select from visit details data*/
  %local visitDetailsDataCont;
  %let visitDetailsDataCont=%str(BOUNCER);
 
  /*Get list of columns to select from initial search results data*/
  %local searchDataCont;
  proc contents data=&formJoinData noprint out=work.cont(
    keep=name where=(upcase(name) not in ("SESSION_SK","SESSION_ATTRIBUTION_SK","DETAIL_SK","SEARCH_RESULTS_DTTM""SEARCH_RESULTS_SK",)));
  run;
  %if %cxa_avae_check_err() %then %goto badEnd;
  proc sql noprint;
  select "f."||strip(lowcase(name)) into: searchDataCont separated by ","
  from work.cont;
  quit;
  %if %cxa_avae_check_err() %then %goto badEnd;
 
  /*Delete target table if it exists.*/
  %if %sysfunc(exist(&outputSrchStarFact)) %then %do;
    proc sql noprint;
    drop table &outputSrchStarFact;
    quit;
    %if %cxa_avae_check_err() %then %goto badEnd;
  %end;
 
  proc sql noprint;
  create table &outputSrchStarFact
  as select
     a.session_attribution_sk
    ,s.session_complete_load_dttm
    ,&sessionAttrCont
    ,&sessionDataCont
    ,&visitDetailsDataCont
    ,&searchDataCont
    ,1 as searches
    ,j.num_additional_searches
    ,j.num_pages_viewed_afterwards
    ,case
      when j.num_pages_viewed_afterwards=0 then 1
      else 0
     end as exit_pages
  from
 
    (((&formJoinData f
 
      inner Join
    &searchJoinData j
      on f.session_attribution_sk=j.session_attribution_sk and
         f.search_results_sk=j.search_results_sk)
 
     inner join
    &sessionAttribution a
      on f.session_attribution_sk=a.session_attribution_sk)
 
      inner join
    &sessionData s
      on f.session_sk=s.session_sk)
 
      left join
    &visitDetailsData v
      on f.session_attribution_sk=v.session_attribution_sk
  ;
  quit;
  %local havErr;
  %let havErr=N;
  %if %cxa_avae_check_err() %then %let havErr=Y;
 
  proc sql noprint;
  drop table &formJoinData;
  drop table &searchJoinData;
  quit;
  %if %cxa_avae_check_err() or &havErr=Y %then %goto badEnd;
 
  %goto macEnd;
 
  %badEnd:
  %if %sysfunc(exist(&searchFormJoin)) %then %do;
    proc sql noprint;
    drop table &searchFormJoin;
    quit;
  %end;
  %if %sysfunc(exist(&searchDetailJoin)) %then %do;
    proc sql noprint;
    drop table &searchDetailJoin;
    quit;
  %end;
  %let load_retvar=ERR;
  /*Note this will only cause the running signal to be deleted if this job if job-flow has not past point of no return.*/
  %cxa_avae_abort(HARD)
  %goto ultEnd;
 
  %macEnd:
  %let load_retvar=OK;
 
  %ultEnd:
%mend cxa_avae_pull_srch_fac;

A fix for this issue for SAS Customer Experience Analytics Server 6.4 is available at: