topic Re: VA 7.4 geomap using SCF in SAS Visual Analytics

VA 7.4 geomap using SCF

jeninemilum — Fri, 15 Sep 2017 17:08:28 GMT

I'm creating geomaps in 7.4 VA. I’ve got SCFs I’d like to use and it only takes 5 digit zips. I’ve tried adding zeros to the end but that really didn’t work. They all showed up in Washington DC. Do you have any suggestions?

Re: VA 7.4 geomap using SCF

FalkoSchulz — Sun, 17 Sep 2017 21:54:22 GMT

I'm not too familiar with SCF's but I doubt that just by adding zeros to the end would make it a valid post code? Also, given details on https://en.wikipedia.org/wiki/Sectional_center_facility some SCF's are served by other state facilities - so just referencing the underlying postcode may give false information.

One way to get this solved would be to download a reference data set such as http://faculty.baruch.cuny.edu/geoportal/data/esri/usa/census/zip3.zip (there may be others if you google) which contains the polygonal areas of SCF's. Either follow the VA admin guide to import these custom polygons into VA or just use the data to extract the coordinates for each SCF. This way you can merge the coordinates back to your source data and create a custom geographical item instead. Something like this I guess:

proc mapimport datafile="C:\temp\zip3.shp" out=data.zip3;
	id ZIP3;
run;

proc sql;
	create table work.scf_coordiantes as
	select distinct zip3, avg(x) as longitude, avg(y) as latitude
	from data.zip3 group by zip3;
quit;run;

Hope this helps!

Cheers, Falko

Re: VA 7.4 geomap using SCF

MichelleHomes — Tue, 19 Sep 2017 06:01:05 GMT

Hi Falko,

I saw your post about SCFs and yesterday @Rick_SAS wrote about SCFs in a zip code blog post he wrote. Thought I'd share it in the thread as he has code on how to create the SCFs.

http://blogs.sas.com/content/iml/2017/09/18/path-of-zip-codes.html

Kind Regards,

Michelle

Re: VA 7.4 geomap using SCF

jeninemilum — Tue, 19 Sep 2017 13:24:39 GMT

Thanks everyone. You all have inspired me to the following solution.

An SCF is the first 3 digits of a zip code. I needed to get a Latitude/Longitude for an SCF. So I took the first and last coordinates of each zipcode in an SCF, divided by 2 and "close enough for my needs" got the geographical center for an SCF and it's associated Lat/Long. These coordinates are used in SAS VA to create a bubble map of the US.

data scf_zip; set sashelp.zipcode(where=(StateCode

NOT IN ("PR", "FM", "GU", "MH", "MP", "PW", "VI")

AND ZIP_Class = " "));

scf = substr(put(zip,z5.),1,3);

keep scf zip x y;

run;

proc sort data=scf_zip; by scf;

run;

data scf(rename=(newx=x newy=y)); set scf_zip;

by scf;

retain x1 y1 0;

if first.scf then do;

x1 = x;

y1 = y;

end;

if last.scf then do;

x2 = x;

y2 = y;

end;

if last.scf then do;

newx = sum(x1,x2)/2;

newy = sum(y1,y2)/2;

output;

end;

keep scf newx newy;

run;

Re: VA 7.4 geomap using SCF

FalkoSchulz — Tue, 19 Sep 2017 21:15:12 GMT

Nice! Glad you got this sorted!!

I also got inspired and couldn't resist trying out the SCF paths @Rick_SAS wrote about in the blog shared by @MichelleHomes using SAS Visual Analytics 8.x. After running the code shared in the blog - I submitted the following to create the final network data set for VA:

proc sql;
	create table geo.scf_paths as
	select scf,xx as longitude, yy as latitude, count(zip) as zipcount, statecode,path
	from work.state
	group by scf;
quit;run;

data geo.scf_paths;
	set geo.scf_paths(where=(path ne .)) end=last;

	length scf_source $5 longitude_source 8. latitude_source 8.;
	length scf_target $5 longitude_target 8. latitude_target 8.;
 
	by scf;

	if first.SCF and _n_ gt 1 then do;
	  scf_target = scf;
	  longitude_target = longitude;
	  latitude_target = latitude;
	  output;
	end;

	scf_source = scf;
	longitude_source = longitude;
	latitude_source = latitude;

	if last then do;
   	  scf_target = "";
	  longitude_target = .;
	  latitude_target = .;
	  output;
	end;

   	retain scf_source longitude_source latitude_source;

   	drop scf longitude latitude; 
run;

In VA 8.x it renders like this:

Colored by network community / node size is number of ZIPsColored by US State

I guess once we know the coordinates for SCF's we have a few more options for interesting visualizations!

Cheers, Falko

Re: VA 7.4 geomap using SCF

MichelleHomes — Tue, 19 Sep 2017 23:02:53 GMT

Great to see and thanks @jeninemilum and @FalkoSchulz for sharing your visualizations!

Falko - I knew you couldn't resist the temptation once you knew what SCFs were

Re: VA 7.4 geomap using SCF

jeninemilum — Wed, 20 Sep 2017 14:01:10 GMT

Am I correct in understanding that you are using the Lat/Long of the first zipcode for the SCF? If you apply some of my logic you can get a more centered Lat/Long for each SCF.

Re: VA 7.4 geomap using SCF

FalkoSchulz — Wed, 20 Sep 2017 21:06:06 GMT

Thanks! Yes, I believe the code at @Rick_SAS blog post is pretty basic and just grabs the first ZIP coordinate but probalby sufficient for this small excercise.

I guess if you want to properly determine the SCF's location you probably need to get the boundary data set as per link in my first reply and determine the centroid for each polygon. After all - a SCF is an area not a location. There are various ways to do this including the official SAS/GRAPH %CENTROID macro, one @SanjayM wrote or via SAS/IML as outlined in another post from Rick.

Since VA 7.4 and upcoming VA 8.2 support custom polygons - the best solution may however to import the boundary data set and render a regional map

Cheers, Falko