Using the API in SAS

Jan Vink over 6 years ago

I hadn't seen any examples of SAS code using the Census API yet, so I wanted to share my piece of code. This code only works with SAS9.4M4 or higher.

The example code underneath creates a table with the number of veterans for all tracts in New York State.

--- SAS Code ---

filename resp temp;
%let myCensusKey = [CensusAPIKey]; /*use your API key here*/

%macro CensusAPIresponseHeaders(responseFile, outTable);

/*puts the results from a responsefile into a table and uses the first row from the response to create variable names */

libname temp JSON fileref=&responseFile;

data _null_;
set temp.root (obs=1);
array elemCols{*} element:;
length rename $4000;
do i=1 to dim(elemCols);
rename=catx(' ',rename, catx('=','element'||compress(put(i,3.)),elemCols{i}));
end;
call symputx('rename',rename);
run;

data &outTable;
set temp.root (firstobs=2);
rename &rename;
run;

libname temp clear;
%mend;

proc http
url="api.census.gov/.../acs5
method= "GET"
out=resp;
run;

%CensusAPIresponseHeaders(resp,ny_vets);

Top Replies

DanKeating 7 months ago +1

I have very much appreciated this macro and have used it repeatedly.

I wanted to pass along that I have made a couple amendments to deal with issues that have arisen.

The first issue is that it is easy…

Parents

Joe Gibson over 6 years ago

Here is an alternate for those lacking the JSON engine (SAS versions before v9.4M4). It strips the brackets from the start and end of each row returned from the API, which makes it as simple CSV file. Then it converts it to a SAS dataset using PROC IMPORT.
Just replace the URL in the "FILENAME ACS_API ..." line below with the URL of your request, run the code, and you'll got your data in as SAS dataset colled MyNewSASDataset.

%let myCensusKey = [CensusAPIKey]; ** use your API key here ;

** Specify the API URL for the data you want. ;
FILENAME ACS_API URL
"api.census.gov/.../acs5 ;

FILENAME _NoBrkts TEMP ; ** Temporary file, in which to save the Census API text, after stripping the leading and closing bracket(s) on each row. ;

** Strip the leading and trailing bracket(s) from each row that the API returns. That turns the file into a simple CSV file. ;
DATA _NULL_ ;
LENGTH _NoBrkts $ 1000. ; ** Make sure that this length is long enough to capture the longest row produced by the API call. ;
INFILE ACS_API ; ** Read the API output. ;
INPUT ; ** Read in a row. ;
FILE _NoBrkts ; ** Direct the subsequent PUT to the temporary text file. ;
_NoBrkts = PRXCHANGE( "s/^\s*\[+|\]+\s*$//", -1, _INFILE_) ; ** Strip the leading and trailing bracket(s) from the row. ;
PUT _NoBrkts ; ** Write the revised line to the temporary text file. ;
RUN ;

PROC IMPORT DATAFILE=_NoBrkts DBMS=CSV OUT=MyNewSASDataset REPLACE ; ** Now just import the revised file as a .csv file. ;
GETNAMES=YES ; ** Get the variable names from the 1st line of the file. ;
RUN ;
Cancel
Up 0 Down

Reply

Cancel

Reply

Joe Gibson over 6 years ago

Here is an alternate for those lacking the JSON engine (SAS versions before v9.4M4). It strips the brackets from the start and end of each row returned from the API, which makes it as simple CSV file. Then it converts it to a SAS dataset using PROC IMPORT.
Just replace the URL in the "FILENAME ACS_API ..." line below with the URL of your request, run the code, and you'll got your data in as SAS dataset colled MyNewSASDataset.

%let myCensusKey = [CensusAPIKey]; ** use your API key here ;

** Specify the API URL for the data you want. ;
FILENAME ACS_API URL
"api.census.gov/.../acs5 ;

FILENAME _NoBrkts TEMP ; ** Temporary file, in which to save the Census API text, after stripping the leading and closing bracket(s) on each row. ;

** Strip the leading and trailing bracket(s) from each row that the API returns. That turns the file into a simple CSV file. ;
DATA _NULL_ ;
LENGTH _NoBrkts $ 1000. ; ** Make sure that this length is long enough to capture the longest row produced by the API call. ;
INFILE ACS_API ; ** Read the API output. ;
INPUT ; ** Read in a row. ;
FILE _NoBrkts ; ** Direct the subsequent PUT to the temporary text file. ;
_NoBrkts = PRXCHANGE( "s/^\s*\[+|\]+\s*$//", -1, _INFILE_) ; ** Strip the leading and trailing bracket(s) from the row. ;
PUT _NoBrkts ; ** Write the revised line to the temporary text file. ;
RUN ;

PROC IMPORT DATAFILE=_NoBrkts DBMS=CSV OUT=MyNewSASDataset REPLACE ; ** Now just import the revised file as a .csv file. ;
GETNAMES=YES ; ** Get the variable names from the 1st line of the file. ;
RUN ;
Cancel
Up 0 Down

Reply

Cancel

Children

No Data