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);
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…
The first issue is that it is easy to get the Name variable more than once in an API call, so I added a line that prevents a repeat of that variable name from crashing the program.
The second is that some variable names in the json had a space in them, like "county subdivision" and that was breaking the rename statement. I just put a compress() to handle that.
The third is that some tables now have more than 1,000 cells. The existing macro has a put (i,3.) that broken when the variable "i" got above 999. I just switched it to put(i,4.).
And the fourth, also related to wide tables (example subject table S1501 with over 1,500 variables), the rename variable in the macro was being truncated, even when I made the length 32000. So I now test to see if the rename variable is full and write overflow to a second rename variable.
Here is the revised macro. Once again, I owe great thanks for the creation of this. I hope my amendments can help others get value from it, as well.
filename resp temp;%macro CensusAPIresponseHeaders(responseFile, outTable);
data _null_; set temp.root (obs=1); array elemCols{*} element:; length rename1 rename2 $32000; do i=1 to dim(elemCols); if elemCols(i) eq 'NAME' then elemcols(i) = strip(elemCols(i)) || put(i,z4.);* fix where 'name' is reused as a variable name in json output; if length(rename1) le 31000 then rename1=catx(' ',rename1, catx('=','element'||compress(put(i,4.)),compress(elemCols{i})));* fix if column has space, ie "county subdivision"; if length(rename1) gt 31000 then rename2=catx(' ',rename2, catx('=','element'||compress(put(i,4.)),compress(elemCols{i})));* fix if column has space, ie "county subdivision"; end;
;* testing;;* rename_len1 = length(rename1);;* rename_len2 = length(rename2);;* put rename_len1 =;;* put rename_len2 =; call symputx('rename1',rename1); call symputx('rename2',rename2);run;
data &outTable; set temp.root (firstobs=2); rename &rename1. &rename2.;run;
libname temp clear;%mend;