Details
  • Views 7395 views
  • Replies 3 replies
  • Subscribers 542 subscribers
  • Users 0 members are here
Labels
Related Census Bureau Resources
Related Forum Threads

Using the API in SAS

Jan Vink
over 5 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);

Parents
  • over 5 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 ;
Reply
  • over 5 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 ;
Children
No Data
For more information about the American Community Survey Census Bureau Website