SAS Studio Custom Task Tuesday: API Calls Made Easy

3 Likes

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. Custom Task Tuesday.jpg

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 button to save, then click the button to open the task. Make your selections, then click again to watch it run!

Want to try it yourself?

Get the code from the zip file at the end of this article or from GitHub.

Take Me to GitHub!

ChrisBrooks · ‎02-04-2017

Combining the API call to download the data with an analysis step is a simple but very effective idea. One problem I have with the Custom Task combobox though is the need to hard code all the options. If only we could specify a data source to load the elements from then it would make for a much more effective tool.

OliviaWright · ‎02-08-2017

@ChrisBrooks Thank you so much for your feedback! I'm passing this message on to those who work on SAS Studio development, but I know they have heard this requirement before and are looking at ways to satisfy it.

However, you should check out the distinct controller (page 18 on the Developer's Guide to Writing Custom Tasks). This controller allows the user to select a variable from a dataset, and then the combobox is populated with the values of that variable. This didn't work for my task specifically because it would require the user to have an input dataset with all UN Countries, Country Codes, etc, but it is a very useful tool that may be helpful to you!