Problem with Census API and SAS JSON library

[ebowen]

Hello! I'm trying to access some Census data using their API, and I'm having a problem getting the proper field names. I'm able to download the data, but the field names end up as the first observation in the dataset. When I look at the API itself, the field names are listed as the "0" observation. I can't figure out how to get SAS to recognize that the first observation is actually the field names. Here's my code so far:

filename cns temp;
proc http
   url='https://api.census.gov/data/2017/acs/acs5?get=NAME,group(B23025)&for=county:*'
   method= "GET"
   out=cns;
run;
libname census JSON fileref=cns;

Thanks for your help!

[Tom · Accepted solution]

Looks like they just wrapped a CSV file into json like syntax.

Why not just reverse the process?

filename csv temp;
data _null_;
  set census.root;
  file csv dsd ;
  put (_all_) (+0);
run;
proc import datafile=csv dbms=csv out=census replace;
run;

[ebowen]

So the Census doesn't have properly formed JSON code. Somehow I'm not surprised.

EDIT: I just found this on the Census API Developer page: "The Census uses a nonstandard version of JSON that is streamlined."

[Tom]

Reminds of a story from when everyone was insisting that data transfers had to use XML.  For an existing system that was using CSV to transfer the files they just wrapped that CSV file as CDATA object in an XML. Problem solved 🙂