How would you set up SAS's Email system options to use an App password rather than the ID and password? ... View more

Thanks Kurt.  While that is a neat tidbit, it seems to only put the 2nd line of the 2-line error message.  For example, when I get the following error   ERROR: Incomplete JSON in input ERROR: Error in the LIBNAME statement.   %put &syserrortext; only prints the 2nd of the two lines in the log   SYMBOLGEN: Macro variable SYSERRORTEXT resolves to Error in the LIBNAME statement. Error in the LIBNAME statement.   Unfortunately, I am trying to distinguish between two different errors, but each has the same 2nd message.   ERROR: Incomplete JSON in input ERROR: Error in the LIBNAME statement.   versus   ERROR: Physical file does not exist, e:\SAS Temporary Files\_TD10912_DESKTOP-575EP3T_\#LN01301. ERROR: Error in the LIBNAME statement. ... View more

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; ... View more

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. ... View more

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. ... View more

Bruno:  I am using SAS 9.4M6 ... View more

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. ... View more