Integrating NHTSA API into SAS Script

[aawve]

Hello,

How do I batch decode a list of VINs using the API from NHTSA? Thank you for your consideration. Details below:

I have a long list (several thousand) of vehicle identification numbers (VINs) as a field in my SAS dataset. I want to decode these VINs in small batches using the API from US DOT (Decode VIN (flat format) in a Batch, API Webpage).

I know how to break the data into small batches, but I can't figure out how to automatically run the list of VINs through the API, mostly because I've never used an API before and hardly understand what is happening.

The input data contains one field of 17-character strings: "5N1AN08W86C562536", "5N1AN08W86C557286", "5N1AN08W86C521324", etc.

The output from the API, based on manual entry of one VIN, contains a bunch of fields, and I don't have a preference about the output format (XML, CSV, JSON, etc.), as long as I can control where it outputs, and that I process it further in SAS.

[ChrisNZ]

Use proc http for each your VIN numbers.

Test which output is easier to use (depending on your SAS version and your skills). 

Start with JSON, it's probably the easiest if your version of SAS support the JSON engine.

[aawve]

Thank you for the prompt reply, ChrisNZ.

I'm missing some of the coding details needed to go from the VINs to the API and back with the decoded information. I've decided that a csv is the format that would work well if a file must be generated to a directory; otherwise, reading the "out = " data directly back into my working session would be ideal.

Please let me know if you have further details or an edited code snippet that works. Details below:

Details:

I prepared a test data set to try to pass through the API one at a time. The test data contains these five values:

5N1AN08W86C562536?format=csv&modelyear=2006
5N1AN08W58C521039?format=csv&modelyear=2008
5N1AN0NW0AC506409?format=csv&modelyear=2010
5NPE24AF3GH306987?format=csv&modelyear=2016
5NPEU46F87H249088?format=csv&modelyear=2007

I tried putting together some code to loop it through the API, as follows:

proc sql
     noprint;
     select
          VINforAPI
          into :VINList separated by ' '
     from test;
quit;

%put &VINList.;

%macro decodeVIN(list);
     %let finish = %sysfunc(countw(&list));
          %do i = 1 %to &finish;
          %let VIN = i;
               proc http
                    method = "POST"
                    url = 'https://vpic.nhtsa.dot.gov/api/vehicles/DecodeVin/'
                    in = '&VIN'
                    out = testdata;
               run;
          %end;
%mend;

%decodeVIN(&VINList);

[ChrisNZ]

You don't need to use a macro.

Something like this should work:

data _null_;
  set VINLIST;
  call execute(' proc http '
             ||' method = "POST" '
             ||' url    = "https://vpic.nhtsa.dot.gov/api/vehicles/DecodeVin/" '
             ||' in     = ' || quote(cats(VIN,'?format=csv&modelyear=',YEAR))
             ||' out    = "VIN_' || VIN || '"; run;' );
run;

Then you can read all the files in one go using an infile wildcard.

[ballardw]

It would be a very good idea to show a screen capture of a submission with multiple vins that works without any SAS intervention, i.e. as you manually enter them.

The VIN list was not submitted for a couple reasons.

First

 %let VIN = i;

means that your Vin macro variable contains "i", not that the macro variable &i would be any better as that would mean values 1 2 3 4 and 5. Not at all the "vin" that the website would expect.

You would want to the ith vin value from the list variable. It may be easier if the website expects your value to be in single quotes to place them in the list values. Not normally my preferred approach but external files expecting single quotes and the SAS macro language can be a headache.

Before actually submitting things to the website I would suggest using %PUT to display the actual text you are creating.

Second, macro variables do not resolve to the text they hold when enclosed in single quotes.

A suggestion for making your VIN values with the quote already part:

data test;
   input VINforAPI $ 1-43 ;
 datalines;
5N1AN08W86C562536?format=csv&modelyear=2006
5N1AN08W58C521039?format=csv&modelyear=2008
5N1AN0NW0AC506409?format=csv&modelyear=2010
5NPE24AF3GH306987?format=csv&modelyear=2016
5NPEU46F87H249088?format=csv&modelyear=2007
;
proc sql
     noprint;
     select
          catt("'",VINforAPI,"'")
          into :VINList separated by '|'
     from test;
quit;

I used the | as a delimiter so later I can use the SCAN function to get the individual values easier:

%macro dummy(list);
%do i = 1 %to %sysfunc(countw(&list.,|));
 %let VIN = %scan(&list.,&i.,|);
 %put the vin is: &vin.;

%end;
%mend;

%dummy(&vinlist.);

If you are going to use macros you must learn to use the Options MPRINT MLOGIC and SYMBOLGEN to get details about the code generated when your macro executes, how the logic evaluates and the construction of macro variable values.

[aawve]

Hello again,

Thank you for replying. Again, I came into this issue with very little idea of what an API is or how to access it, but my understanding is a bit closer now. Through some combination of trial and error based on your suggestions, I put together the following snippets that worked.

/* This snippet:                                                                          */
/*      subsets the long list of VINs into manageable chunks for the API;                 */
/*      (create the subset by picking row numbers of the dataset, i.e., row 1 - row 50)   */
/*      creates the API call for each VIN;                                                */
/*      and, converts the dataframe field into a list                                     */

%macro run_VINs(startobs, endobs);

data VINSubset (where = (row_num >= &startobs & row_num <= &endobs));
	set unique_VINs;
	VINforAPI = cats("https://vpic.nhtsa.dot.gov/api/vehicles/DecodeVinValues/",ShortVIN,"?format=csv");
	row_num = _N_;
	keep ShortVIN row_num VINforAPI;
run;

proc sql
	noprint;
	select
		VINforAPI
	into :VINList separated by '|'
	from VINSubset;
quit;

%mend;

/* This snippet:                                                                          */
/*      iterates through each API call in the list                                        */
/*      it pulls out the VIN from the call and uses that substring to make an output file */
/*      it runs each API call through the API and writes the results to the output file   */
/*      finally, it imports the output file into SAS for further processing               */

%macro decodeVIN(list);

	%do i = 1 %to %sysfunc(countw(&list.,|));
		%let VIN = %scan(&list., &i, |);
		%let output = path\to\my\output\VIN_%substr(&VIN, 57, 9).csv;
		filename V&i "&output";
		proc http
			method = "GET"
			url = "&VIN"
			out = V&i;
		run;
		proc import 
			out = work._VIN&i
  			datafile = "path\to\my\output\VIN_%substr(&VIN, 57, 9).csv"
  			dbms = csv REPLACE;
  			getnames = yes; 
		run;

%mend;