SAS Studio Custom Task Tuesday: API Calls Made Easy
The Custom Task we will look at this week downloads the user-specified data from the public United Nations Comtrade database, and then does a trade flow analysis on that data.
Before I created this task, I already had all of the SAS code to pull the data from the API. I found myself running and re-running my program changing only a few things, every time I wanted to download data from a different country or time period. That’s when I realized this program was a perfect candidate for becoming a Custom Task.
In the interest of saving space for this blog post, we will only be adding the three drop down combo boxes and not the information and maximum records objects. However, you will be able to download my full task code at the end of the post.
This is what the finished product will look like when we are done:
Here is what some sample output from our finished task would look like:
Note that this API is subject to change at any time, and the changes may impact the functionality of this task. You can find updated release notes on changes that have been made here.
Step 1: Getting Started
In SAS Studio, under the Task and Utilities section, open a “New Task” as well as the “Sample Task.” We will copy and paste the necessary Velocity Template code from the Sample Task to our task.
Step 2: Naming and Saving the Task
Name: UN Comtrade Analysis
Description: This task downloads the user-specified data from the public United Nations Comtrade database, and then does a trade flow analysis on that data.
At the top of the VTL code for your New Task, you will need to fill in the Name and Description portions as shown below:
After you’ve done that, you should save this task to your My Tasks folder, so you don’t lose it. Click the button in the upper left corner of the task to bring up this option screen:
Step 3: Creating the Metadata Portion of the Task
To make things easier on ourselves, we are going to steal VTL code from the Sample Task. From our “finished product,” you can see that we are going to use three comboboxes (we will leave out the information and maximum records for simplicity). Find the 6 lines of code that correspond with a combobox in the Metadata section of the Sample Task, and copy and paste them into the same place in your task 3 times. Edit the code you copied to correspond with what we want as our finished product.
This is what your finished Metadata portion should look like:
<Metadata>
<DataSources>
</DataSources>
<Options>
<Option inputType="string" name="GROUPCOMBO1">REPORTING AREA</Option>
<Option inputType="string" name="labelCOMBO1">Choose a reporting area below. </Option>
<Option defaultValue="all" inputType="combobox" name="comboRArea" width="100%">Reporting Area: </Option>
<Option inputType="string" name="all">All</Option>
<Option inputType="string" name="156">China</Option>
<Option inputType="string" name="484">Mexico</Option>
<Option inputType="string" name="842">USA</Option>
<Option inputType="string" name="GROUPCOMBO2">PARTNERING AREA</Option>
<Option inputType="string" name="labelCOMBO2">Choose a partnering area below. </Option>
<Option defaultValue="0" inputType="combobox" name="comboPArea" width="100%">Partnering Area: </Option>
<Option inputType="string" name="0">World</Option>
<Option inputType="string" name="GROUPCOMBO3">TIME PERIOD</Option>
<Option inputType="string" name="labelCOMBO3">Choose a time period below. </Option>
<Option defaultValue="2015" inputType="combobox" name="comboPeriod" width="100%">Time Period: </Option>
<Option inputType="string" name="2015">2015</Option>
<Option inputType="string" name="2014">2014</Option>
<Option inputType="string" name="2013">2013</Option>
</Options>
</Metadata>Step 4: Creating the UI Portion of the Task
Each object that we just put code for in the metadata portion will have corresponding code in the UI section. Just like we did in the previous step, find the 8 lines of code that correspond with a combobox in the UI section of the Sample Task, and copy and paste them into the same place in your task 3 times. Edit to code you copied to correspond with what we want as our finished product.
This is what your finished UI portion should look like:
<UI>
<Group option="GROUPCOMBO1" open="true">
<OptionItem option="labelCOMBO1"/>
<OptionChoice option="comboRArea">
<OptionItem option="all"/>
<OptionItem option="156"/>
<OptionItem option="484"/>
<OptionItem option="842"/>
</OptionChoice>
</Group>
<Group option="GROUPCOMBO2" open="true">
<OptionItem option="labelCOMBO2"/>
<OptionChoice option="comboPArea">
<OptionItem option="all"/>
<OptionItem option="156"/>
<OptionItem option="484"/>
<OptionItem option="842"/>
</OptionChoice>
</Group>
<Group option="GROUPCOMBO3" open="true">
<OptionItem option="labelCOMBO3"/>
<OptionChoice option="comboPeriod">
<OptionItem option="2015"/>
<OptionItem option="2014"/>
<OptionItem option="2013"/>
</OptionChoice>
</Group>
</UI>Step 5: Creating the Code Template Portion of the Task
This is the portion of the task that contains your SAS Code. The approach I took to reading in the data was to use the URL option in a filename statement, and use several SAS macro variables for different pieces of the URL. The important thing to note here is how the SAS code works with the VTL code. Velocity Template Language has its own macro variables, and each of our UI elements has one. When you select an option in the UI, the value of the VTL macro variable will change immediately. For this task, I chose to create SAS macro variables with the values of the VTL macro variables because I already had this SAS code before creating the task, but it is not necessary.
The UN API Documentation outlines what each piece of the URL means, which I used when writing this API call.
/*&ps time period: default = now*/ %let ps='$comboPeriod';
/*&r reporting area: default = 0*/ %let r='$comboRArea';
/*&p partner area: default = all*/ %let p='$comboPArea';
data _null_;
call symput('url', compress('https://comtrade.un.org/api/get?max=500'||'&'||'type=C'||'&'||'freq=A'||'&'||'px=HS'||'&'||'ps='||&ps||'&'||'r='||&r||'&'||'p='||&p||'&'||'rg=all'||'&'||'cc=total'||'&'||'fmt=csv',' '));
run;
%put %SUPERQ(url);
filename file url "%SUPERQ(url)";
data comtrade;
length Commodity $ 120 Qty_Unit $ 20 Trade_Flow $ 10;
infile file dlm=',' dsd firstobs=2;
input
Classification $
Year
Period
Period_Desc $
Aggregate_Level
Is_Leaf_Code
Trade_Flow_Code
Trade_Flow $
Reporter_Code
Reporter $
Reporter_ISO $
Partner_Code
Partner $
Partner_ISO $
Second_Partner_Code $
Second_Partner $
Second_Partner_ISO $
Customs_Proc_Code $
Customs $
Mode_Transport_Code $
Mode_Transport $
Commodity_Code $
Commodity $
Qty_Unit_Code
Qty_Unit $
Qty $
Alt_Qty_Unit_Code $
Alt_Qty_Unit $
Alt_Qty $
Netweight $
Gross_weight $
Trade_Value
CIF_Trade_Value $
FOB_Trade_Value $
Flag ;
format Trade_Value dollar16.0;
run;
proc sort data=comtrade;
by trade_flow;
run;
ods path(prepend) work.templat(update);
ods graphics on;
proc template;
define statgraph sgdesign;
dynamic _Trade_Flow _Trade_Value;
begingraph / designwidth=608 designheight=523 dataskin=none;
entrytitle halign=center 'Distribution of Trade Value by Trade Flow';
layout lattice / rowdatarange=data columndatarange=data rowgutter=10 columngutter=10;
layout overlay / xaxisopts=( labelattrs=(style=NORMAL weight=BOLD ) discreteopts=( tickvaluefitpolicy=splitrotate)) yaxisopts=( labelattrs=(style=NORMAL weight=BOLD ));
boxplot x=_Trade_Flow y=_Trade_Value / name='box' groupdisplay=Cluster fillattrs=(color= CXDA6D00) medianattrs=(thickness=1 color=black ) whiskerattrs=(thickness=1 color=black) meanattrs=(weight=normal color=black);
endlayout;
endlayout;
endgraph;
end;
run;
proc sgrender data=comtrade template=sgdesign;
dynamic _Trade_Flow="Trade_Flow" _Trade_Value="Trade_Value";
run;
Step 6: Run the UN Comtrade Task
You’re finished! You just created a custom user interface to download data from a public API, turn it in to a dataset, and analyze it. Click the 
Want to try it yourself?
Get the code from the zip file at the end of this article or from GitHub.
