Combine state counties to regions

[#1]

Kansas has 105 counties that are combined into 16 Health preparedness regions. How can I do this using the US-County-Name.jmp and the associated XY file?

The 16 regions for Kansas are defined at the following link: https://www.kdhe.ks.gov/ArchiveCenter/ViewFile/Item/1246

Any help would be appreciated.

[#2]

Somebody may have an easier way, but I've done things like this and know it will work though it takes some effort.  First, I would download that region table you linked and then separate the counties using the comma as a delimiter (I usually use Excel for this stage, although it can be done in JMP).  Then I would restructure that table by stacking the counties so that there are 2 columns, one with the region and the other showing counties associated with each region (but as a single column).  Then you can make sure that the US-County-Name.jmp file has the same format for the county names as the file you created (such as adding the word "County" to the county name - make sure it matches exactly the way the county names look in the map file).  Then assign the map role to the County column you have created, linking it to the US-County-Name.jmp file using the correct named column in that file.  Since your new file will have a column for Region, you can use that in Graph Builder to overlay or color the regions on the map.

I know this is labor intensive but it's the only way I've done this in the past.

[#3]

Hello @jkeighley ,

Below I have some code that needs some debugging, but should be a solid place to start. Any other scripters that could improve the script below?  I'm sure there is much improvement needed!

1. Opens US-County-Name.jmp & preps for searching later, by breaking apart state and county into new columns
2. Downloads & opens the PDF you linked above, then imports the table on the first page
   1. Note - always review script before running on your local machine! This script downloads a PDF from the internet.  Check line 15, the URL string in the URL() argument
3. Assigns creates an associative array (AA) of regions (keys) & counties (values) within the region <-- loop not working
   1. I'm a beginning at regular expressions, so the regex expression is broken
   2. The list of counties comes back as a single string, which regex is supposed to break apart
      1. County string example: "Barton, McPherson, Pawnee, Rice, Saline, Stafford"
      2. Needs to be list of strings: { "Barton", "McPherson", "Pawnee", "Rice", "Saline", "Stafford" }
4. In US-County-Name.jmp...
   1. Uses a few nested loops to find rows where State = KS and other conditions
   2. When a row where State = KS, look at country, then finds which region it corresponds to by searching through the AA and then assigning the region (key) the county (value) is assigned to
   3. Saves the Region (key) to the row's Region column

names default to here(1);
clear log();

/* This block opens & readies US-County-Name.jmp */
countylist = open( "C:/Program Files/SAS/JMP/17/Maps/US-County-Name.jmp" );
countylist << New column( "County", formula( Word( 1, :Full Name Short, " " ) ) );
countylist << New column( "Region", Character );
countylist << New column( "State", formula( Word( -1, :Full Name Short, " " ) ) );

/* This block DOWNLOADS & opens the downloaded PDF & imports first page table */
request = New HTTP Request(
	URL(
		"https://www.kdhe.ks.gov/ArchiveCenter/ViewFile/Item/1246"
	),
	Method( "Get" )
);
file = request << Download( "$TEMP/Kansas Health Preparedness Regions.pdf", "replace", "show_progress" );
dt = Open( file,
		PDF Tables(
		Table(
			table name(
				"Kansas Health Preparedness Regions and Map Page(1)  Table(1)"
			),
			add rows( page( 1 ), Rect( 0.4789, 1.0156, 10.5211, 7.8894 ) )
		)
	)
);

// For some reason, dt imports as a 1 item list of data tables
// Reassigns dt to a "Table" data type for further processing
dt = dt[1];

// Deletes empty rows in imported PDF table
dt << Select where( Is Missing( :Region ) ) << Delete Rows;

// Starting AA for data storage
region = Associative array();

// Stores region / county list data in AA
for( i = 1, i <= N Rows( dt ), i++,
	
	templist = {};
	temphold = dt:Counties Included[i];
	tempitems = N Items( words( dt:Counties Included[i], "," ) );
	show(tempitems);
	
	// Breaks apart country string into individual counties and adds them to templist
	for( n = 1, n <= tempitems, n++,
		iteration = eval( eval expr( expr( eval insert( "\^n^" ) ) ) );

        // Fix me! This is broken
		insert into( templist, regex( "^\!"(\w*),*\!"$", iteration ) );
	);

	// Save the list as the value pair (county list, list of strings) to the respective key
	region[ dt:region[i] ] = templist
);

// The region & county list is now stored in an AA!
//print( region );

/* This block will use data in AA to assign region to respective county in US-County-Name.jmp */
for( k = 1, k <= N rows( countylist ), k++,

	// Condition where state = Kansas
	try( if( countylist:State[k] == "KS", 
		
		// Iterates through AA keys (regions)
		for( m = 1, m <= N Items( kskeys ), m++,
			
			// Creates list of counties within m region
			tempvals = region[m];
			
			// Checks if Count present, then assigns region to dt
			if( contains( tempvals, countylist:County[k] ), // County is in m list
				
				countylist:Region[k] = kskeys[m]
				
			);			
		);		
	) ); // try --> if county not found in PDF stated counties, no region will be assigned
);

// Closes out pdf table, if needed
close( dt, "No Save" );

[#4]

@jkeighley 

I got the script below to successfully update the regions into US-County-Name.jmp. Note that the script below does not save this file, or modify the associated XY file.

names default to here(1);
clear log();

/* This block opens & readies US-County-Name.jmp */
countylist = open( "C:/Program Files/SAS/JMP/17/Maps/US-County-Name.jmp" );
countylist << New column( "County", formula( Word( 1, :Full Name Short, " " ) ) );
countylist << New column( "Region", Character );
countylist << New column( "State", formula( Word( -1, :Full Name Short, " " ) ) );

/*  This block opens the downloaded PDF */
// Opens downloaded PDF & imports the first table
request = New HTTP Request(
	URL(
		"https://www.kdhe.ks.gov/ArchiveCenter/ViewFile/Item/1246"
	),
	Method( "Get" )
);
file = request << Download( "$TEMP/Kansas Health Preparedness Regions.pdf", "replace", "show_progress" );
dt = Open( file,
		PDF Tables(
		Table(
			table name(
				"Kansas Health Preparedness Regions and Map Page(1)  Table(1)"
			),
			add rows( page( 1 ), Rect( 0.4789, 1.0156, 10.5211, 7.8894 ) )
		)
	)
);

// For some reason, if the below command doesn't re-assign dt to the data table,
// dt isn't the correct data type for the operation to continue
dt = dt[1];

// Deletes empty rows in imported PDF table
dt << Select where( Is Missing( :Region ) ) << Delete Rows;

// Starting AA for data storage
region = Associative array();

// Stores region / county list data in AA
for( i = 1, i <= N Rows( dt ), i++,
	
	// Save the list as the value pair (county list, list of strings) to the respective key
	region[ dt:region[i] ] = words( dt:Counties Included[i], ", " );
	
);

// The region & county list is now stored in an AA!
//print( region );

kskeys = region << Get keys;

/* This block will use data in AA to assign region to respective county in US-County-Name.jmp */
for( k = 1, k <= N rows( countylist ), k++,

	// Condition where state = Kansas
	try( if( countylist:State[k] == "KS",
		
		// Iterates through AA keys (regions)
		for( m = 1, m <= N Items( kskeys ), m++,
				
			// Checks if Count present, then assigns region to dt
			if( contains( region[ kskeys[m] ], countylist:County[k] ), // County is in m list
				
				countylist:Region[k] = kskeys[m]
				
			);			
		);		
	) ); // try --> if county not found in PDF stated counties, no region will be assigned
);

// Closes out pdf table, if needed
close( dt, "No Save" );