Warning of column truncated 8times

[imdickson]

Hi everyone. I have a script that will prompt me warning of column 'geometry' truncated 8 times.

the code :

data ncp_dim_ba_geo (drop=excp_code cgis_area)
     excp.excp_ncp_dim_ba_geo;
  length excp_code $50;
  length gis_ba dotpos $50 zone $2 state_code $5
         region state business_area_code $30 business_area $50 xval yval 8;
  if _n_=1 then do;
     declare hash h(dataset:"business_area");
     h.definekey('gis_ba');
     h.definedata('region','zone','state_code','state','business_area_code','business_area');
     h.definedone();
     call missing(region,zone,state_code,state,business_area_code,business_area);
  end;
  set hpsncp.DIST_OPERATION_BND_GIS (keep=cgis_area geometry cgis_label
      dbsastype=(cgis_area='CHAR(50)' geometry='CHAR(32767)' cgis_label='CHAR(50)'));
  if cgis_area='0290 - Sg. Petani' then cgis_area = '6290 - SUNGAI PETANI';
  if cgis_label='PERLIS' then cgis_area = '2610 - PERLIS';
  gis_ba=upcase(cgis_area);
  rc2=h.find();
  if (rc2 ne 0) then do;
     excp_code='Exception: Unable to map business area';
     output excp.excp_ncp_dim_ba_geo;
  end;
  if (rc2=0);
  geometry=substr(geometry,15);
  seq=1;
  do until(dotpos eq '');
     dotpos=compress(scan(geometry, seq, '[]'),',','nk');
     if dotpos not in ('' '{' '}' '[' ']' ',') then do;
        xval=input(put(scan(dotpos,1,','),20.),20.)/1000;
        yval=input(put(scan(dotpos,2,','),20.),20.)/1000;
        if xval ne . and yval ne . then do;
           output ncp_dim_ba_geo;
        end;
     end;
     seq+1;
  end;
  keep excp_code cgis_area region zone state_code state business_area_code business_area seq xval yval;
run;

I tried defining geometry as 32767 character in length statement but I am still getting warning of :

NOTE: There were 126 observations read from the data set 
WARNING: Column 'geometry' was truncated 8 times.  Observation (row) number 2 was the first truncated.

What did i do wrong? I just dont get it.

[Patrick]

@imdickson 

Why are you using DBSASTYPE for all source columns. That should only be necessary for special cases where you need SAS to convert a source data type into something else than the default. ....or do you use DBSASTYPE because you have an issue with the column lengths created by SAS? If so then this can get controlled with libname options.

As for the warning: I'm not sure but based on your code the only reason I can see for such a warning is that your source string actually exceeds the 32KB limit in 8 cases. If so then the only way to avoid this is to split up the source string and read the remainder into a 2nd variable. You would need to get this done via explicit SQL pass-through.

Below your code with some amendments. Nothing major except for having the call missing() always executed to ensure all columns used in the hash only are always set to missing before a new lookup.

data ncp_dim_ba_geo (drop=excp_code cgis_area)
     excp.excp_ncp_dim_ba_geo;

  if _n_=1 then do;
     if 0 then set business_area(keep=gis_ba region zone state_code state business_area_code business_area);
     declare hash h(dataset:"business_area");
     h.definekey('gis_ba');
     h.definedata('region','zone','state_code','state','business_area_code','business_area');
     h.definedone();
  end;
 call missing(of _all_);


  length excp_code $50 dotpos $50 $50 xval yval 8;;

  set hpsncp.DIST_OPERATION_BND_GIS (keep=cgis_area geometry cgis_label
        dbsastype=(cgis_area='CHAR(50)' geometry='CHAR(32767)' cgis_label='CHAR(50)'));

  if cgis_area='0290 - Sg. Petani' then cgis_area = '6290 - SUNGAI PETANI';
  if cgis_label='PERLIS' then cgis_area = '2610 - PERLIS';
  gis_ba=upcase(cgis_area);

  if (h.find() ne 0) then do;
     excp_code='Exception: Unable to map business area';
     output excp.excp_ncp_dim_ba_geo;
     return;
  end;

  geometry=substr(geometry,15);

  do seq=1 by 1 until(dotpos eq '');
     dotpos=compress(scan(geometry, seq, '[]'),',','nk');
     if dotpos not in ('' '{' '}' '[' ']' ',') then do;
        xval=input(put(scan(dotpos,1,','),20.),20.)/1000;
        yval=input(put(scan(dotpos,2,','),20.),20.)/1000;
        if xval ne . and yval ne . then do;
           output ncp_dim_ba_geo;
        end;
     end;
  end;
  keep excp_code cgis_area region zone state_code state business_area_code business_area seq xval yval;
run;

And last but not least: It looks like you're parsing a JSON string. If so then maybe a RegEx could make things "simpler". 

[BillM_SAS]

Or using the JSON LIBNAME engine.