Resources

Selecting All Financial Ratios Time Series

/*----------------------------------------------------------------

                   SAS SAMPLE LIBRARY

       Name: quanex03.sas
Description: Example program from SAS/ETS User's Guide,
             The SASEQUAN Interface Engine
      Title: Selecting All Financial Ratios Time Series
             From Multiple Quandl Codes
    Product: SAS/ETS Software
       Keys: Quandl data extraction
      Procs: SASEQUAN
      Notes: Read this before you run this sample.
             The sample data provided resides in the ets/sasmisc folder.
             If you are using automap=READONLY, then you must copy the
             map of your incoming XML data to a writeable folder before
             using it. So, define a Windows system environment variable
             ,QUANDL, to the path of your  writeable folder containing
             the QUANEX03.MAP file and QUANEX03.xml.
             To assign a fileref to the external file to be processed,
             use the following form of the libname statement:

           libname quan sasequan "%sysget(QUAN)"
              OUTXML=quanex03
              AUTOMAP=replace
              MAPREF=MyMap
              XMLMAP="%sysget(QUAN)quanex03.map"
              APIKEY='<your Quandl apikey>'
              IDLIST='<your list of QUANDL codes (separated by commas>'
              FORMAT=xml
              START='<trim_start>'
              END='<trim-end>'
              ;
 ----------------------------------------------------------------*/

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 vars*/
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 var 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;