The SASEQUAN Interface Engine

This example shows how to retrieve data for multiple time series that have different Quandl codes. The time series for each Quandl code are named identically in the original Quandl data set, and many exceed the limit of 32 bytes for a valid SAS variable name. Names such as "3-Year Standard Deviation of Stock Price," "Cash as Percentage of Firm Value," "Cash as Percentage of Revenues," and "Sales General and Administration Expenses" are truncated to 30 bytes before appending the suffix _1 to the Google data and _2 to the IBM data. Long variable names are represented using the long-name convention of "literal n variable name n".

The resulting SAS data set contains the merged contents of both Quandl data sets; the first Quandl data set variable names have the appended suffix _1, and the second Quandl data set variable names have the appended suffix _2. The SASEQUAN engine appends the appropriate suffix, _N, for every Quandl code that appears in the IDLIST= option, where N is the positional number of the Quandl code in the IDLIST= option. The Quandl codes are separated by commas in the IDLIST= option. The output is shown in Output 43.3.1.

title 'All Financial Ratios for Google and IBM';
libname _all_ clear;
options validvarname=any;
libname mylib "U:\quan950\doc\";

libname myTwo sasequan "%sysget(QUANDL)"
   OUTXML=tallfin
   AUTOMAP=replace
   MAPREF=MyMap
   XMLMAP="%sysget(QUANDL)tallfin.map"
   APIKEY='XXXXXXXXXXXXXXXXXXXX'
   IDLIST='DMDRN/GOOG_ALLFINANCIALRATIOS,DMDRN/IBM_ALLFINANCIALRATIOS'
   FORMAT=xml
   START='2003-12-31'
   END='2012-12-31'
   ;

data mylib.tallFin;
   set myTwo.tallfin;
run;

/* Using PROC CONTENTS to obtain alphabetical order of variable names              */
proc contents data=mylib.tallFin out=newa(keep=name); run;

/* Create macro variables for each variable name and count the number of variables */
data _null_;
   set newa end=last;
   k+1;
   call symput('var'||trim(left(put(k,8.))),trim(name));
   if last then call symput('total',trim(left(put(k,8.))));
run;

%macro test;
   %do i=1 %to &total;
       %put var&i is: &&var&i;
   %end;

   /*-- Use the QUOTELST macro to put " "N around the elements of a variable list --*/

   %macro quotelst(strng,quote1=%str(%"),quote2=%str(%"N),delim=%str( ));
      %local i quotelst;
      %let i=1;
      %do %while(%length(%qscan(&strng,&i,|)) GT 0);
         %if %length(&quotelst) EQ 0
         %then %let quotelst=&quote1.%qscan(&strng,&i,|)&quote2;
         %else %let quotelst=&quotelst.&quote1.%qscan(&strng,&i,|)&quote2;
         %let i=%eval(&i + 1);
      %if %length(%qscan(&strng,&i,|)) GT 0 %then %let quotelst=&quotelst.&delim;
      %end;
      %unquote(&quotelst)
   %mend quotelst;

   /*-- macro variable longname&i holds all the N-literal SAS variable names, --*/
   /*-- which are now in alphabetical order                              --*/

   %do i=1 %to &total;
       %let longname&i=%quotelst(&&var&i);
       %put LONGNAME&i is: &&longname&i;
   %end;

   %let total=%eval(&total-1); /* handle date variable separately */

   /*-- RETAIN statement gives the preferred order of variables,          --*/
   /*-- with date first, and the rest in alphabetical order....           --*/
   /*-- shuffling to get each MSFT series next to each matching IBM series--*/

   data mylib.orderFin;
      retain date;
      retain %do j=1 %to &total;
          &&longname&j
           %end;;
      set mylib.tallFin;
   run;

   proc print data=mylib.orderFin;
   run;
%mend test;

/* Invoke the macro */

%test;

The results before using the %test macro are shown in Figure 43.2, but you can use the test macro to generate a different order of the results. In Output 43.3.1, the TEST macro shuffles the financial ratios of company 1 (Google) with the ratios for company 2 (IBM) by listing their respective time series side by side. The %QUOTELST macro preserves the embedded blanks within variable names, whereas the RETAIN statement orders the time series for easy side-by-side comparison, as shown in Output 43.3.1.