PROC HTTP to access Census Trade data

[BhargavDesai]

Hello Gurus,

I am trying to run following code to access publicly available Census data -  I get BAD request.   When I paste URL in chrome,  it gets me the data.

Thanks in advance

proc http

  method="get"

  url='https://api.census.gov/data/timeseries/intltrade/imports/hs'

  query=(

    "get" = "CTY_CODE,CTY_NAME,CON_QY1_MO,

             I_COMMODITY,I_COMMODITY_SDESC,I_COMMODIT,

             GEN_VAL_MO"

    "COMM_LVL" = "HS10"

    "YEAR"="2025"

"MONTH"="01"

    "I_COMMODITY" = "76*"

  )

  verbose

  out=resp

;

run;

URL:

https://api.census.gov/data/timeseries/intltrade/imports/hs?get=CON_QY1_MO,CTY_CODE,CTY_NAME,I_COMMO...

[ballardw]

Best practice on this forum is to show us the LOG generated when submitting the code. The exact wording of error or warning messages is important as they often include important clues as to possible solutions.

I suggest that you re-run the code and if the issue persists to copy from the log the code and all messages related to proc http. On the forum open a text box using the </> icon that appears above the message window and paste the copied text.

You don't show a definition for the OUT=resp. Somewhere prior to this you should have a Filename statement creating a reference to a file that the "get" will write to for later processing.

[DaveHorne]

Adding debug level=3; shows an error in one of the parameters:

< 0000020F76230395: 65 72 72 6F 72 3A 20 75 6E 6B 6E 6F 77 6E 20 76 error: unknown v
< 0000020F762303A5: 61 72 69 61 62 6C 65 20 27 49 5F 43 4F 4D 4D 4F ariable 'I_COMMO
< 0000020F762303B5: 44 49 54 27                                     DIT'

When I copy the exact URL string that you're using with Chrome, it works ok:

filename resp temp;                                                                                                                             
%let parms=?get=CON_QY1_MO,CTY_CODE,CTY_NAME,I_COMMODITY,I_COMMODITY_SDESC,UNIT_QY1,GEN_VAL_MO&YEAR=2025&MONTH=01&COMM_LVL=HS10&I_COMMODITY=76*;
proc http                                                                                                                                       
  method="get"                                                                                                                                  
  url="https://api.census.gov/data/timeseries/intltrade/imports/hs&parms"                                                                       
  verbose                                                                                                                                       
  out=resp                                                                                                                                      
;                                                                                                                                               
run;

[Ksharp]

Interesting .

Although there is an ERROR in your LOG.

5    proc http
6      method="get"
7      url='https://api.census.gov/data/timeseries/intltrade/imports/hs?get=CON_QY1_MO,CTY_CODE,CTY_NAME,I_COMMODITY,I_COMMODITY_SDESC,UNIT_QY1,GEN_VA
7  ! L_MO&YEAR=2025&MONTH=01&COMM_LVL=HS10&I_COMMODITY=76*'
8      verbose
9     out=out
10    headerout=hdrout;
URL                  =
https://api.census.gov/data/timeseries/intltrade/imports/hs?get=CON_QY1_MO,CTY_CODE,CTY_NAME,I_COMMODITY,I_COMMODITY_SDESC,UNIT_QY1,GEN_VAL_MO&YEAR=20
25&MONTH=01&COMM_LVL=HS10&I_COMMODITY=76*
METHOD               = get
Out                  = c:\temp\Output.txt
Header Out           = c:\temp\Response.txt

11   debug level=3;
12   run;

ERROR: 连接至“148.129.75.191:443”时出错。(连接超时。)
ERROR: 无法连接 Web 服务器。

But the json file you want to download is already in there , check this:

filename out "c:\temp\Output.txt";
 
filename hdrout "c:\temp\Response.txt";

proc http                                                                                                                                       
  method="get"                                                                                                                                  
  url='https://api.census.gov/data/timeseries/intltrade/imports/hs?get=CON_QY1_MO,CTY_CODE,CTY_NAME,I_COMMODITY,I_COMMODITY_SDESC,UNIT_QY1,GEN_VAL_MO&YEAR=2025&MONTH=01&COMM_LVL=HS10&I_COMMODITY=76*'                                                                    
  verbose                                                                                                                                       
 out=out
 headerout=hdrout;
debug level=3;                                                                                                                                               
run;

Therefore you could ignore this ERROR info I guess.

[Tom]

Look at the RESP file to see the error message that the API you are querying generated.

Two things. 

• There does not appear to be a variable named I_COMMODIT that you are requesting.
• It does not like the spaces in the GET= option.

Also it looks like it includes all of the extra variables you mention in the filters, so you can remove I_COMMODITY from the list of variables provided to GET=.

filename resp temp;

proc http
  method="get"
  url='https://api.census.gov/data/timeseries/intltrade/imports/hs'
  query=(    
"get" = "CTY_CODE,CTY_NAME,CON_QY1_MO,I_COMMODITY_SDESC,GEN_VAL_MO"
"COMM_LVL" = "HS10"
"YEAR"="2025"
"MONTH"="01"
"I_COMMODITY"="76*"
  )
  verbose
  out=resp
;
run;

Once you have the file it looks like you can read it simply by just removing the square brackets.

data want;
 infile resp dsd firstobs=2 truncover  ;
 input @;
 _infile_=compress(_infile_,'[]');
 input (CTY_CODE CTY_NAME CON_QY1_MO I_COMMODITY_SDESC GEN_VAL_MO COMM_LVL YEAR MONTH I_COMMODITY)
       (:$100.)
 ;
run;

[BhargavDesai]

Thanks. I will try them out

[LukeDalton]

Hello,
I think you have to put the entire endpoint into the quoted URL string (I've never used a "Query" option in PROC HTTP before). i.e., url='https://api.census.gov/data/timeseries/intltrade/imports/hs?get=CON_QY1_MO,CTY_CODE,CTY_NAME,I_COMMO...'