Join the Pinball Wizards with SAS
Long before video games were invented the most high-tech form of gaming available was the 
Additionally towards the end of the article you will find some suggestions for further analyses you can do yourself with data from this API. So if you are learning SAS and find you cannot attend your usual place of work or learning as a result of the Covid-19 pandemic then why not install SAS OnDemand for Academics and try some of these? If you hit a problem simply post a question in the appropriate support forum and one of the many experts here will try to help you out.
Get started with SAS OnDemand for Academics
Get the Data
Instead of a collection of CSV files the pinballmap web site has an extensive API which allows you to download a large selection of data from its web site. The API is fully featured but its documentation is a little sparse so some trial and error was required to decide which call to use and what parameters were required.
Firstly, using Proc HTTP I downloaded JSON formatted data for all regions to discover what region 
filename regs temp;
proc http
url="http://pinballmap.com/api/v1/regions.json"
method= "GET"
out=regs;
run;
libname regions JSON fileref=regs;
proc sql;
select *
from regions.regions
where state="Nevada";
quit;
Then I get information about all the machines in the database
filename macs temp;
proc http
url="http://pinballmap.com/api/v1/machines.json"
method= "GET"
out=macs;
run;
libname macs JSON fileref=macs;
Now I retrieve the data for all locations in Las Vegas
filename locs temp;
proc http
url="http://pinballmap.com/api/v1/region/lasvegas/locations.json"
method= "GET"
out=locs;
run;
libname locs JSON fileref=locs;
In order to find out which machines are in each location I can get a cross reference file – however as you can see that holds machine and location IDs rather than names
filename locmacs temp;
proc http
url="http://pinballmap.com/api/v1/region/lasvegas/location_machine_xrefs.json"
method= "GET"
out=locmacs;
run;
libname locmacs JSON fileref=locmacs;
Getting the Data Ready
The data required very little preparation after the initial download. I noticed that the location data file contained locations in Las Vegas, Henderson, North Las Vegas and Mesquite. These are not in the file in error - the lasvegas parameter used in the call refers to a a wider area than just the city of Las Vegas itself. I therefore used Proc SQL to create a subset of the location data containing selected fields and restricted to data from only the city of Las Vegas.
proc sql;
create table vegaslocs
as select name as location_name, id as location_id
from locs.locations
where city = "Las Vegas";
quit;The Results
In order to produce the detail I require I used Proc SQL to join three tables using the cross reference table to act as the link point for the location and machine names.
proc sql;
create table vegasmacs
as select
a.location_name,
b.name as machine_name,
c.condition
from vegaslocs a,
macs.machines b,
locs.locations_location_machine_xre c
where
b.id=c.machine_id and
a.location_id=c.location_id
;
quit;
You can see from the resulting table that I now have a list of every machine at each location along with a brief note about the machines condition.
Ideas for Further Analysis
There is a lot of data available through this API - why not explore it and come up with some analyses of your own? Here are a few ideas to get you started
- Use Proc SGMap to create a map of all locations in Las Vegas where there are pinball machines;
- Extract details of all Las Vegas based machines for a selected manufacturer;
- Find out the average age of machines at each location;
- Choose 10 machines at random from the machines data set and work out how you could play each machine while visiting the fewest locations
Visit [[this link]] to see all the Free Data Friday articles.
