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