Choropleth map - FIPS data in plotting not recognized

[Indescribled]

I am trying to take the Tidy Tuesday data for this week and turn it into a choropleth map of the US.

I think everything works correctly up until PROC SGMAP at which time I get this error: ERROR: Variable FIPS not found

I checked via PROC PRINT to make sure fips is there, and it seems to be, so I am unsure of what to do next. 

* Get data 1;
filename test1234 url "https://raw.githubusercontent.com/rfordatascience/tidytuesday/master/data/2021/2021-10-05/nurses.csv";

proc import out=nurses datafile=test1234 dbms=csv replace; 
	guessingrows = max; 
run;

* Filter to 2020 only;
data nurses;
	set nurses;
	where Year = 2020;
run;

* Solution here to get FIPS data with state name
https://communities.sas.com/t5/SAS-Programming/Convert-Full-State-Name-to-Abbreviation/td-p/738723;

data fips;
  fmtname='$FIPS';
  length fips 8 label $2 start $20 ;
  do fips=1 to 95;
    start=fipnamel(fips);
    if start ne 'Invalid Code' then do;
       label=fipstate(fips);
       output;
    end;
  end;
run;

proc format cntlin=fips ; run;

data test;
 set fips;
 statecode=put(start,$fips.);
run;

* Merge nurses data with fips data;
proc sql;
	create table work.nurse_fips as
	select t1.State,
		   t1.'Total Employed RN'n,
		   t2.fips,
		   t2.start
	from nurses t1
		left join work.fips t2 on (t1.State = t2.start);
quit;

* Confirming output to make sure fips column exists due to error;
proc print 
	data=work.nurse_fips;
run;

* Plot template
* https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/grmapref/n01dh0rm86m35un12p8j7dz9ugog.htm;

data states;
  set maps.states;
  if state ^in(2,15,72);
  x = -x * 45/atan(1); 
  y =  y * 45/atan(1);
run;

data plot_data; 
set maps.uscenter;
  if state ^in(2,15,72) and ocean^='Y';
  long = -long;
  statename = fipstate(state);
run;

* New error is: "ERROR: Variable FIPS not found";
proc sgmap mapdata=states
  maprespdata=work.nurse_fips
  plotdata=plot_data;

  esrimap
  url='http://services.arcgisonline.com/arcgis/rest/services/
Canvas/World_Light_Gray_Base';

  choromap 'Total Employed RN'n / mapid=fips density=2
    name='choro';

  text x=long y=lat text=statename /
    textattrs=(size=6pt);

  gradlegend 'choro' / title='Total Employed RN, 2020' 
	   extractscale;

run;

quit;

[Oligolas]

Hi,

unfortunately I do not have sgmap installed to test it and I'm not familiar with it, but I get everything to work until then.

I needed to add:

options validvarname=any;"

and to mount the maps libname

libname MAPS "C:\Program Files\SASHome\SASFoundation\9.4\maps";

Since you are in a "validvarname=any;" environment, does SGMAP maybe require a "SAS name literal" to work?

It's strange to me that SAS does not find the variable so, I'm afraid this is the only idea I have right now, maybe it's worth a test.

like this?

choromap 'Total Employed RN'n / mapid='fips'n density=2

[ballardw]

The MAPS library I have from SAS/Graph does not have a MAPS.STATES data set.

Please show the Proc Contents result when run on MAPS.STATES.

You say you "checked via PROC PRINT to make sure fips is there". Which data set(s) did you check? There are three involved.

Note very closely that the example you borrowed from did not use FIPS but STATE as the MAPID variable and the programmer added that to the PLOT_DATA data set build from MAPS.USCENTER. The MAPS.USCENTER I have does not have a FIPS variable. What may be more important, is the USCENTER set, at least the one I have, only has state level data. If you are attempting to use FIPS at a level other than state then you may be wasting your time completely. Or share the Proc Contents from your MAPS.USCENTER.

I believe we may have mentioned that when you get an error then show the code from the log that generated the error message along with all the notes and messages from that procedure as well.

[Indescribled]

Long response, but I tried to answer each of your questions!

Here is the output of

proc contents
	data = maps.states;
run;

When I ran PROC PRINT to look for the fips variable, I first checked the dataset in the example I linked to see how it was done there. The "state" variable is the FIPS id of the state, but the column title is state instead of fips. I assumed that meant that SAS required the FIPS number to plot it. 

Below is the output of 

proc print 
	data=sashelp.us_data;
run;

I ran the code below to confirm that my newly created table had the same format as the data used in the example. 

proc print 
	data=work.nurse_fips;
run;

Then, in the plot procedure, I replaced 

maprespdata= to my new data instead of the population data set

choromap to my data instead of the population data, instead of the population data in the example

mapid= my new fips variable, which was previously called "state" in the example

The data I am working with is only state level, there is no county data.

Here is:

proc contents
	data = maps.uscenter;
run;

Here are the exact errors/logs

[Indescribled · Accepted solution]

After a lot of trial and error I managed to get it to work. Here is the full working code and explanation if anyone else runs across this in the future:

The issue I seemed to be having was that choromap mapid=??? NEEDS to be "state". Even if I call it fips and the contents of the column are the state fips ID, the column MUST be named state. I named it fips and it did not work or even recognize that it existed. 

In the end I merged my data with the data from the plot example page because I knew for sure the plot example data worked, so all I had to do was get my state level data merged into it. An issue arose where they both had "State" as a column name, so I had to rename the nurses data state, because as mentioned before, the fips ID must be in a column named "state".

This was frustrating to figure out, but now that I know how it works it should be easier in the future.

* Get data 1;
filename test1234 url "https://raw.githubusercontent.com/rfordatascience/tidytuesday/master/data/2021/2021-10-05/nurses.csv";

proc import out=nurses datafile=test1234 dbms=csv replace; 
	guessingrows = max; 
run;

data nurses;
	set nurses;
	where Year = 2020;
run;

* Renaming state column to something else to try to merge later with another dataset that has a STATE column too;
data nurses2;
   set nurses (rename=(State=StateNameFull));
run;

PROC SQL;
	create table work.nurse_fips3 as
	select t1.StateNameFull,
		   t1.'Total Employed RN'n,
		   t2.STATENAME,
		   T2.STATE,
		   T2.STATECODE
	from nurses2 t1
		left join sashelp.us_data t2 on (t1.StateNameFull = t2.statename);
quit;

data states;
  set maps.states;
  if state ^in(2,15,72);
  x = -x * 45/atan(1); 
  y =  y * 45/atan(1);
run;

data plot_data; 
set maps.uscenter;
  if state ^in(2,15,72) and ocean^='Y';
  long = -long;
  statename = fipstate(state);
run;

title 'Total Employed RN by State, 2020';
proc sgmap mapdata=states
  maprespdata=work.nurse_fips3
  plotdata=plot_data;

  esrimap
  url='http://services.arcgisonline.com/arcgis/rest/services/
Canvas/World_Light_Gray_Base';

  choromap 'Total Employed RN'n / mapid=state density=2
    name='choro';

  text x=long y=lat text=statename /
    textattrs=(size=6pt);

  gradlegend 'choro' / title='Total Employed RN, 2020' 
	   extractscale;

run;

quit;

[Oligolas]

Hi, 

the only differences I see right now is that you renamed fips to state and added Statecode to Nurse_fips3

Since there are no differences in Plot_data and only renaming changes in the SGMAP call, I presume the problem was that you need Statecode to get SGMAP to run.

I can not see why fips could be the problem unless SGMAP would actually expect this syntax, I don't know.

On the other hand I see that Guam's and Virgin Island's fips values are now missing, where they had values of respectively 66 and 78 before.

Maybe you'd like to look over this.