I am trying to read from Hive database that happens to have a single column consisting 60k characters. Since SAS can only support up to 32k words limit, as per forum advice, I split the column into 3 different columns using pass thru SQL statement. Then, in the next Data step, i concat these 3 columns and process. However, my output table is always blank.
What the script does it to read GPS coordinates from one column in Hive and my SAS Script will split the coordinates into row based with 1 set of LNG LAT in 1 row tying to 1 specific location.
What could be wrong here? Why is it always blank?
My Code:
proc sql;
connect to hadoop as mydb
(server=<removed>
database=<removed>
);
create table a as
select * from connection to mydb(
select cgis_area
,substr(geometry,1,30000) as geometry1
,substr(geometry,30001,30000) as geometry2
,substr(geometry,60001,30000) as geometry3
from GIS where cgis_area = '6224 - Bagan Serai' );
disconnect from mydb;
quit;
proc sort data=ncpdm.ncp_dim_ba
(keep=region zone state_code state business_area_code business_area gis_ba)
out=business_area nodupkeys;
by gis_ba business_area_code;
run;
data business_area;
set business_area;
by gis_ba business_area_code;
if first.business_area_code;
run;
proc sort data=ncpdm.ncp_dim_ba
(keep=region zone state_code state business_area_code business_area gis_ba)
out=business_area nodupkeys;
by gis_ba business_area_code;
run;
data business_area;
set business_area;
by gis_ba business_area_code;
if first.business_area_code;
run;
data ncp_dim_ba_geo (drop=excp_code cgis_area)
work.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 a;
array geo {3} geometry1-geometry3;
** CREATE AND POPULATE VARCHAR FIELD **;
length longgeo varchar(160000);
do i = 1 to 3;
/* longgeo = cat(longgeo,geo(i));*/
longgeo = cat(geo(i));
end;
/* longgeo=cat(geo1,geo2,geo3);*/
/* set hpsncp.GIS (keep=cgis_area geometry cgis_label*/
/* dbsastype=(cgis_area='CHAR(50)' geometry='CHAR(32767)' cgis_label='CHAR(50)'));*/
gis_ba=upcase(cgis_area);
rc2=h.find();
if (rc2 ne 0) then do;
excp_code='Exception: Unable to map business area';
output work.excp_ncp_dim_ba_geo;
end;
if (rc2=0);
longgeo=substr(longgeo,15);
seq=1;
do until(dotpos eq '');
dotpos=compress(scan(longgeo, 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;
Am i doing something wrong? Mind u that before changing to longchar type in datastep, it was working fine. The reason why i change to Longchar(160000) is due to Character limit count. If i revert back to the original code without longchar and concatenation, it will produce data perfectly.
Click image to register for webinar
