Census API uses '&' parameter - how to work around in a macro

[texasmfp]

Hi:

I am trying to develop code to download the full set of Census import data each month.  Unfortunately, Census limits the volume of data that can be called via the API.  As a result, that means I will need to run the code a few thousand times, substituting a few of the parameters with each iteration.  However, the Census API uses the '&' to define the parameters.  I am hoping to create a macro that will minimize the required code by doing the substituting for me.  Obviously, SAS's macro '&' conflicts.

Here is the code:

DM 'CLEAR LOG; CLEAR OUTPUT'; RESETLINE;
options ExtendObsCounter=no
NOQUOTELENMAX;

LIBNAME COMPANY2 'W:\SAS Data\';        

filename in url 'https://api.census.gov/data/timeseries/intltrade/imports/hs?get=YEAR,MONTH,RP,CTY_CODE,CTY_SUBCODE,DISTRICT,DIST_NAME,CON_QY1_MO,CON_QY1_YR,I_COMMODITY,I_COMMODITY_SDESC,I_COMMODITY_LDESC,CON_VAL_MO,CON_VAL_YR,CON_CHA_MO,CON_CHA_YR,
GEN_VAL_MO,CAL_DUT_MO,CAL_DUT_YR
&SUMMARY_LVL2=HSCYCSDTRP&COMM_LVL=HS10&time=2021-11&CTY_CODE=1220&I_COMMODITY=12*';

libname in json;

data COMPANY2.bingo_out;
set in.root;
run;

I will need to change the saved filename (currently bingo_out) as well as the values for three variables:

&time=2021-11

&CTY_CODE=1220

&I_COMMODITY=12*';

Thanks

[Tom · Accepted solution]

Since it looks like you are already generating some SAS code (FILENAME statement) then do it with a DATA step where you can work with the & in an actual variable instead of a macro variable. Protecting macro triggers will be much easier in SAS code than macro code.  

So build the string using data step code, like CATS() for example.  And then either use it directly to make the FILEREF.

data _null_;
  time='2021-11';
  CTY_CODE='1220';
  I_COMMODITY='12*';
  length url $500;
  url = cats('https://api.census.gov/data/timeseries/intltrade/imports/hs'
            ,'?get=YEAR,MONTH,RP,CTY_CODE,CTY_SUBCODE,DISTRICT,DIST_NAME'
            ,',CON_QY1_MO,CON_QY1_YR,I_COMMODITY,I_COMMODITY_SDESC'
            ,',I_COMMODITY_LDESC,CON_VAL_MO,CON_VAL_YR,CON_CHA_MO,CON_CHA_YR'
            ,',GEN_VAL_MO,CAL_DUT_MO,CAL_DUT_YR'
            ,'&SUMMARY_LVL2=HSCYCSDTRP'
            ,'&COMM_LVL=HS10'
            ,'&time=',time
            ,'&CTY_CODE=',cty_code
            ,'&I_COMMODITY=',i_commodity
            );
   rc=filename('in',url,'url');
run;

Or create a macro variable with value inside of single quotes where the macro processor will ignore the & and % characters.

....
 call symputx('url',quote(trim(url),"'"));
run;
libname in url &url ;

I think you will find that you will need to copy the result of your URL to an actual physical file to get the JSON libref engine to work with it.  Something like this might work.

filename json temp;
%let rc=%sysfunc(fcopy(in,json));
libname json json;
proc copy inlib=json out=work; 
run;

But it might be easier to use PROC HTTP to copy the file.

[texasmfp]

Tom:

Thanks  as always for your insight.  I am using your suggestion and working with the following code, but there are two issues, which hopefully have simple resolutions.

First, I am manually changing the value for I_COMMODITY from '12*' to '13*' to '14*', etc. I want to get a macro to do the work for me.  Let's say the I_Commodity values is a list from '12*' to '13*' and up to 98* (by 1).  I then want to repeat those I_COMMODITY values again for the next CTY_CODE value (which is also a list) '1220', to '1221' to 1222' etc... Then, of course run those all again for a series of 7 time values (i.e., 2021-11, then 2020-11, then 2020-12, 2019-12, etc)?

Second, the root file has the variable names as ordinal_root, element1, element2, etc.  The first row has the actual variable names I want to use, "YEAR", "MONTH", "RP", "CTY_CODE", etc.... which are the field names I am "GET"ting.  How do I get the 1st row values to be the variable names rather than an observation?

DM 'CLEAR LOG; CLEAR OUTPUT'; RESETLINE;
options ExtendObsCounter=no;

LIBNAME COMPANY2 'W:\SAS Data\';        

data _null_;
  time='2021-11';
  CTY_CODE='1220';
  I_COMMODITY='16*';
  length url $500;
  url = cats('https://api.census.gov/data/timeseries/intltrade/imports/hs'
            ,'?get=YEAR,MONTH,RP,CTY_CODE,CTY_SUBCODE,DISTRICT,DIST_NAME'
            ,',CON_QY1_MO,CON_QY1_YR,I_COMMODITY,I_COMMODITY_SDESC'
            ,',I_COMMODITY_LDESC,CON_VAL_MO,CON_VAL_YR,CON_CHA_MO,CON_CHA_YR'
            ,',GEN_VAL_MO,CAL_DUT_MO,CAL_DUT_YR'
            ,'&SUMMARY_LVL2=HSCYCSDTRP'
            ,'&COMM_LVL=HS10'
            ,'&time=',time
            ,'&CTY_CODE=',cty_code
            ,'&I_COMMODITY=',i_commodity
            );
   rc=filename('in',url,'url');
run;

filename json temp;
%let rc=%sysfunc(fcopy(in,json));
libname json json;

proc copy inlib=json out=work; 
run;

proc append base=company2.impdetl data=ROOT force;
run;

[Tom]

Again using SAS code is much easier than macro code.

Since you appear to want to transfer many of these files setup a macro that allows you to transfer one of them.

Perhaps something like:

%macro get_census(url,out);
...
filename in url &url;
....
data &out;
 ....
run;
%mend ;

Then use a data step to do your looping and generate the URL needed and then use CALL EXECUTE() or just PUT statement into a text file to generate a series of macro calls.

data census;
  start='01NOV2020'd;
  format start date9.;
  do offset=0 to 6;
     time=put(intnx('month',start,offset),yymm7.);
     do COMMODITY=12 to 16;
       I_COMMODITY=cats(COMMODITY,'*');
       do cty_code='1220','1221','1245' ;
          dsn=catx('_','census',time,commodity,cty_code);
          output;
       end;
     end;
   end;
run;

data _null_;
  set census;
  length url $500;
  url = cats('https://api.census.gov/data/timeseries/intltrade/imports/hs'
            ,'?get=YEAR,MONTH,RP,CTY_CODE,CTY_SUBCODE,DISTRICT,DIST_NAME'
            ,',CON_QY1_MO,CON_QY1_YR,I_COMMODITY,I_COMMODITY_SDESC'
            ,',I_COMMODITY_LDESC,CON_VAL_MO,CON_VAL_YR,CON_CHA_MO,CON_CHA_YR'
            ,',GEN_VAL_MO,CAL_DUT_MO,CAL_DUT_YR'
            ,'&SUMMARY_LVL2=HSCYCSDTRP'
            ,'&COMM_LVL=HS10'
            ,'&time=',time
            ,'&CTY_CODE=',cty_code
            ,'&I_COMMODITY=',i_commodity
            );
   call execute(cats('%nrstr(%get_census)(',url,',',dsn,')'));
run;

[Tom]

If you want help parsing the JSON the the URL generates then provide some examples of what it looks like.  The little bit I see on the website makes it kind of look like they have just stuffed a CSV file into the middle of a JSON wrapper.  So you might want to extract the text from the JSON and write to an actual text file where it will be easier to read.

[texasmfp]

Thanks Tom.  I have found that all fields are being read in as characters and, for some fields each API call can yield varying lengths.  When appending subsequent API calls to my database, the varying lengths results in a truncated warning and, the data shows that character strings are being cut off.  It looks like I will need to create something called a map to change a few character fields to numeric and to set a length.  I think I will create a separate post for that.

[Tom]

Also long as you can write the data out as a delimited text file that might be the simplest way to handle those types of inconsistencies.

Take your exsiting inconsistent datasets and write them to CSV files.  Use one file if they all have the same variables in the same order.  Or perhaps add a RETAIN statement to force them to all have the same variables in the same order (even if the types and/or lengths are different).

filename csv temp;
data _null_;
  retain id var1 var2 ... ;
  set file1;
  file csv dsd mod;
  put (_all_) (+0);
run;
data _null_;
  retain id var1 var2 ... ;
  set file2;
  file csv dsd mod;
  put (_all_) (+0);
run;
...

Now just write your own data step to read the CSV file and define the variable how you want them defined.

data want;
  infile csv dsd truncover ;
  length id 8 var1 $10 var2 $40  ... ;
  input (_all_) (+0);
run;

[texasmfp]

Thanks Tom.  I found an even simpler solution.  The issue was that the first macro iteration set the lengths, since there was no previously existing BASE file.  So I created the BASE file before the macro runs, setting the lengths at the max.  Now, nothing gets truncated when it is appended.

I am down to my last issue.  I am running over 1,000 API calls through the macro iterations.  Invariably, for whatever reason, there is a glitch with a few of the API calls.  Maybe it is the interwebs, maybe it is the Census server, whatever the cause, it fails.  Ideally, I would like the macro to recognize the failure and run it again, until it succeeds.  But if push comes to shove, I'd at least like the log to print a list of the failed runs at the end so that I don't have to scroll through 10000's of log lines to figure out which ones failed.  Then, armed with the list, I could at least manually run them.  Thanks in advance for any suggestions.

MPRINT(GET_EXAMPLES):   filename json temp;
MLOGIC(GET_EXAMPLES):  %LET (variable name is RC)
MPRINT(GET_EXAMPLES):   libname json json;
NOTE: JSON data is only read once.  To read the JSON again, reassign the JSON LIBNAME.
ERROR: Physical file does not exist, e:\SAS Temporary Files\_TD12064_DESKTOP-575EP3T_\#LN02903.
ERROR: Error in the LIBNAME statement.
MPRINT(GET_EXAMPLES):   proc copy inlib=json out=work;
MPRINT(GET_EXAMPLES):   run;

ERROR: Libref JSON is not assigned.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE COPY used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

NOTE: The SAS System stopped processing this step because of errors.

[Tom]

If the issue is the LIBNAME statement then capture and/or print the value of SYSLIBRC after running the LIBNAME statement.

The other thing to do is to check the resulting dataset for missing time periods.

Say your macro is currently appending NEXT to BASE by running something like:

proc append base=base data=next force;
run;

Just make sure that NEXT and BASE have variables to store you API settings that matter.  If the data does not already have variables that can identify which API call generated those records then add them yourself.   Say your macro/loop is using two macro variables named PARM1 and PARM2 to determine which API call to make. Just add dataset variables named PARM1 and PARM2 and populate them with the values of the macro varaibles.

data next;
  set next;
  parm1=symget('parm1');
  parm2=symget('parm2');
run;

[texasmfp]

Thanks Tom:  the issue is not that some variables are missing data, the issue is nothing results from the API call - its a failed call. 

Taking your suggestion, I can use the syslibrc to print in the log whether it failed or not (see code box).  Rather than print something to the log, what I really want is to have the macro not go on to the next step if the value for syslibrc ne 0, but to loop back to retry the filename statement and keep looping back until the API call works (i.e., the syslibrc =0).

filename json temp;
%let rc=%sysfunc(fcopy(in,json));
libname json json;

%if &syslibrc ne 0
      %then %put "Could not connect to &product";
      %else %put "Successfully connected to &product";

proc copy inlib=json out=work; 
run;

[BrunoMueller]

I suggest to use Proc HTTP with the QUERY option to download what you need. See the code below for an example. Using the query option it is quite easy to provide name=value pairs in the url.

%let time = 2021-11;
%let cty_code = 1220;
%let I_commodity = 12*;
filename resp temp;

proc http
  method="get"
  url='https://api.census.gov/data/timeseries/intltrade/imports/hs'
  query=(
    "get" = "YEAR,MONTH,RP,CTY_CODE,CTY_SUBCODE,DISTRICT,DIST_NAME,CON_QY1_MO,CON_QY1_YR,I_COMMODITY,I_COMMODITY_SDESC,I_COMMODITY_LDESC,CON_VAL_MO,CON_VAL_YR,CON_CHA_MO,CON_CHA_YR,GEN_VAL_MO,CAL_DUT_MO,CAL_DUT_YR"
    "SUMMARY_LVL2" = "HSCYCSDTRP"
    "COMM_LVL" = "HS10"
    "time"="&time"
    "CTY_CODE" = "&CTY_CODE"
    "I_COMMODITY" = "&I_COMMODITY"
  )
  verbose
  out=resp
;
run;

libname resp json;

proc copy in=resp out=work;
run;

libname resp clear;
filename resp clear;

[texasmfp]

Bruno:

Thanks but I get a Syntax Error using (my mod) of your code:

%macro get_examples(period,country,product);

%let time = .
%let cty_code = &country;
%let I_commodity = &product*;
filename resp temp;

proc http
  method="get"
  url='https://api.census.gov/data/timeseries/intltrade/imports/hs'
  query=(
    "get" = "YEAR,MONTH,RP,CTY_CODE,CTY_SUBCODE,DISTRICT,DIST_NAME,CON_QY1_MO,CON_QY1_YR,I_COMMODITY,I_COMMODITY_SDESC,I_COMMODITY_LDESC,CON_VAL_MO,CON_VAL_YR,CON_CHA_MO,CON_CHA_YR,GEN_VAL_MO,CAL_DUT_MO,CAL_DUT_YR"
    "SUMMARY_LVL2" = "HSCYCSDTRP"
    "COMM_LVL" = "HS10"
    "time"="&time"
    "CTY_CODE" = "&CTY_CODE"
    "I_COMMODITY" = "&I_COMMODITY"
  )
  verbose
  out=resp
;
run;

libname resp json;

proc copy in=resp out=work;
run;

libname resp clear;
filename resp clear;

proc append force base=impdetl2 data=ROOT;
run;

%mend get_examples;
%get_examples(2021-11,*,0101)
%get_examples(2021-11,*,0102)

44   %get_examples(2021-11,*,0101)
NOTE: Line generated by the invoked macro "GET_EXAMPLES".
1                          proc http   method="get"   url='https://api.census.gov/data/timeseries/intltrade/imports/hs'
1  ! query=(     "get" =
     -----
     22
WARNING: Apparent symbolic reference KEY not resolved.
NOTE: Line generated by the invoked macro "GET_EXAMPLES".
1     filename resp temp;  proc http   method="get"   url='https://api.census.gov/data/timeseries/intltrade/imports/hs'
1  ! query=(     "get" =
     -----
     76
ERROR 22-322: Syntax error, expecting one of the following: ;, AUTH_ANY, AUTH_BASIC, AUTH_NEGOTIATE, AUTH_NONE, AUTH_NTLM,
              CLEAR_CACHE, CLEAR_CONN_CACHE, CLEAR_COOKIES, CT, EXPECT_100_CONTINUE, FOLLOWLOC, HEADERIN, HEADEROUT,
              HEADEROUT_OVERWRITE, HTTP_TOKENAUTH, IN, METHOD, NOFOLLOW, NOFOLLOWLOC, NO_CONN_CACHE, NO_COOKIES, OAUTH_BEARER,
              OUT, PASSWORD, PROXYHOST, PROXYPASSWORD, PROXYPORT, PROXYUSERNAME, PROXY_AUTH_BASIC, PROXY_AUTH_NEGOTIATE,
              PROXY_AUTH_NONE, PROXY_AUTH_NTLM, TIMEOUT, URL, USERNAME, VERBOSE, WEBAUTHDOMAIN, WEBPASSWORD, WEBUSERNAME.

ERROR 76-322: Syntax error, statement will be ignored.


WARNING: RUN statement ignored due to previous errors. Submit QUIT; to terminate the procedure.
NOTE: PROCEDURE HTTP used (Total process time):
      real time           0.04 seconds
      cpu time            0.03 seconds

NOTE: The SAS System stopped processing this step because of errors.
NOTE: JSON data is only read once.  To read the JSON again, reassign the JSON LIBNAME.
ERROR: Physical file does not exist, e:\SAS Temporary Files\_TD19132_DESKTOP-575EP3T_\#LN00054.
ERROR: Error in the LIBNAME statement.



ERROR: Libref RESP is not assigned.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE COPY used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

NOTE: The SAS System stopped processing this step because of errors.
WARNING: Libref RESP is not assigned.
NOTE: Fileref RESP has been deassigned.


ERROR: File WORK.ROOT.DATA does not exist.

NOTE: Statements not processed because of errors noted above.
NOTE: The data set WORK.IMPDETL2 has 0 observations and 0 variables.
NOTE: PROCEDURE APPEND used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds

NOTE: The SAS System stopped processing this step because of errors.

[BrunoMueller]

What version of SAS are you using?

The doc says this about the QUERY option:

The QUERY= option is available beginning with SAS Viya 3.5 and the November 2019 release of SAS 9.4M6

So might have to do with this.

[texasmfp]

Bruno:  I am using SAS 9.4M6