The SASEQUAN Interface Engine

Example 50.3 Selecting IBM and Google Quandl Codes for All Financial Ratios

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 50.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;

Output 50.3.1: All Financial Ratios for Google and IBM, Side by Side, Shuffled Order

All Financial Ratios for Google and IBM

Obs date 3-Year Regression
Beta_1
3-Year Regression
Beta_2
3-year Standard
Deviatio_4_1
3-year Standard
Deviatio_73_2
Book Debt to
Capital Ratio_1
Book Debt to
Capital Ratio_2
Book Value of
Assets_1
Book Value
of Assets_2
Book Value of
Equity_1
Book Value
of Equity_2
Capital Expenditures_1 Capital Expenditures_2 Cash as Percentage
of Fi_10_1
Cash as Percentage
of Fi_79_2
Cash as Percentage
of Revenues_1
Cash as Percentage
of Revenues_2
Cash as Percentage
of To_12_1
Cash as Percentage
of To_81_2
Cash_1 Cash_2 Change in Non-Cash
Worki_13_1
Change in Non-Cash
Worki_82_2
Correlation
with the Market_1
Correlation
with the Market_2
Current PE Ratio_1 Current PE Ratio_2 Depreciation_1 Depreciation_2 Dividend
Yield_1
Dividend Yield_2 Dividends_1 Dividends_2 EBIT for Previous
Period_1
EBIT for Previous
Period_2
EV To Sales
Ratio_1
EV To Sales
Ratio_2
EV to Book Value
Ratio_1
EV to Book Value
Ratio_2
EV to EBIT Ratio_1 EV to EBIT Ratio_2 EV to EBITDA
Ratio_1
EV to EBITDA
Ratio_2
EV to Invested
Capital Ratio_1
EV to Invested
Capital Ratio_2
EV to Trailing
Sales Ratio_1
EV to Trailing
Sales Ratio_2
Earnings Before
Interest_19_1
Earnings Before
Interest_21_1
Earnings Before
Interest_88_2
Earnings Before
Interest_90_2
Effective Tax
Rate on Income_1
Effective Tax
Rate on Income_2
Effective Tax
Rate_1
Effective Tax
Rate_2
Enterprise Value_1 Enterprise Value_2 Expected Growth
in Earni_30_1
Expected Growth
in Earni_99_2
Expected Growth
in Revenues_1
Expected Growth
in Revenues_2
Firm Value_1 Firm Value_2 Forward Earnings
Per Share_1
Forward Earnings
Per Share_2
Forward PE Ratio_1 Forward PE Ratio_2 Free Cash Flow
to Firm_1
Free Cash Flow
to Firm_2
Growth in
Earnings Per
Share_1
Growth in
Earnings Per
Share_2
Hi-Lo Risk_1 Hi-Lo Risk_2 Insider Holdings_1 Insider Holdings_2 Institutional
Holdings_1
Institutional
Holdings_2
Invested Capital_1 Invested Capital_2 Market Capitalization_1 Market Capitalization_2 Market Debt to
Capital Ratio_1
Market Debt
to Capital Ratio_2
Market Debt to
Equity Ratio_1
Market Debt
to Equity Ratio_2
Net Income_1 Net Income_2 Net Margin_1 Net Margin_2 Non-Cash Working
Capital_1
Non-Cash
Working Capital_2
Non-Cash Working
Capital_119_2
Non-Cash Working
Capital_50_1
Number of
Shares Outstanding_1
Number of Shares
Outstanding_2
PE to Growth
Ratio_1
PE to Growth
Ratio_2
Payout
Ratio_1
Payout Ratio_2 Pre-Tax Operating
Margin_1
Pre-Tax Operating
Margin_2
Previous Year
Growth in_107_2
Previous Year
Growth in_38_1
Price to Book
Value Ratio_1
Price to Book
Value Ratio_2
Price to Sales
Ratio_1
Price to Sales
Ratio_2
Ratio of Fixed
Assets to_103_2
Ratio of Fixed
Assets to_34_1
Ratio of Intangible
Asse_111_2
Ratio of Intangible
Asse_42_1
Reinvestment
Amount_1
Reinvestment
Amount_2
Reinvestment
Rate_1
Reinvestment
Rate_2
Return on Capital_1 Return on Capital_2 Return on Equity_1 Return on Equity_2 Revenues_1 Revenues_2 Sales General
and Admini_130_2
Sales General
and Admini_61_1
Stock Price_1 Stock Price_2 Total Debt_1 Total Debt_2 Trading Volume_1 Trading Volume_2 Trailing 12-month
Revenues_1
Trailing 12-month
Revenues_2
Trailing Net
Income_1
Trailing Net
Income_2
Trailing PE
Ratio_1
Trailing PE
Ratio_2
Trailing Revenues_1 Trailing Revenues_2 Value Line
Beta_1
Value Line
Beta_2
1 2003-12-31 0.00 1.70 0.0000 0.3289 0.010834 0.45891 871.5 104457 . . 176.8 4393 0.00635 0.04083 0.12536 0.08237 . . 334.7 7647 -4.435 253.577 . 1.03375 499.086 21.4948 43.9 4701 0 0.007336 0 1220.04 246.27 10352.41 35.7292 2.01529 86.5235 3.63663 85.069 12.1476 79.4074 9.2173 155.417 4.09644 19.6170 1.93482 615.68 659.58 14786.83 19487.83 . . 0.60000 0.29989 52375.4 179624.7 . 0.115 . 0.095 52710.1 187271.7 2.11 5.31 93.5071 18.4840 117.81 10406.83 . 0.070 0.35502 0.10163 0.6130 0.01 0.1144 0.5458 337.0 43849 52703.5 163639.7 0.000125 0.12619 0.000125 0.14441 105.6 7613 0.07204 0.08541 -5.4 6097 0.068405 -0.003684 235.0 1694.5 . 1.86911 0 0.16026 0.42000 0.16590 0.1159 2.3358 97.5629 5.8728 35.9530 1.83595 0.14062 0.21606 0.07175 0.12117 128.46 -54.42 0.52164 -0.00526 0.73078 0.23609 0.17524 0.27322 1465.9 89131 22929 268.3 197.30 98.15 6.6 23632 5627185 1872600 2669.9 92838 190.6 0.0 276.514 . 2669.9 92838 0.00 1.05
2 2004-12-31 0.00 1.82 0.0000 0.3107 0.000648 0.43526 3313.4 109183 . . 319.0 4368 0.01691 0.06860 0.40608 0.11200 0.64354 0.09681 2132.3 10570 143.910 -93.490 . 1.17155 310.686 15.1756 128.5 4915 0 0.009735 0 1316.48 534.05 7903.21 38.8740 1.49045 43.0260 2.92535 110.687 12.7109 99.2951 8.8559 155.223 3.40870 23.6102 1.52069 1120.07 1248.57 11291.11 16206.11 . . 0.52320 0.30005 123977.0 143520.0 0.635 0.095 0.465 0.075 126109.3 154090.0 6.35 5.33 65.3323 15.4184 199.64 8543.70 . 0.060 0.44223 0.15940 0.0409 0.01 0.4728 0.5384 798.7 42104 126107.4 131163.0 0.000015 0.14879 0.000015 0.17480 405.9 8643 0.12727 0.08976 222.6 4701 0.048820 0.069798 277.3 1645.6 4.89269 1.59744 0 0.15232 0.35121 0.11726 0.1125 0.8434 43.0533 4.4093 39.5420 1.36212 0.13899 0.11435 0.09366 0.05852 334.41 -640.49 0.62618 -0.08104 0.66865 0.18771 0.13857 0.29055 3189.2 96293 24737 611.6 414.86 82.18 1.9 22927 7507209 4456000 5251.0 94378 0.0 8643.0 . 15.1756 5251.0 94378 0.00 1.10
3 2005-12-31 . 1.64 . 0.1858 0.000000 0.40620 10271.8 105748 9419.0 33098 838.2 3842 0.05709 0.08112 1.30880 0.15017 0.78216 0.12942 8034.2 13686 -114.693 68.163 . 0.80000 92.689 17.1465 256.8 5188 0 0.012931 0 1888.80 1433.74 6533.10 21.6180 1.70108 14.9420 3.02682 62.974 16.5343 56.1331 10.6445 95.829 3.68644 14.2408 1.73028 2107.30 2364.10 9376.00 14564.00 . . 0.31963 0.30321 132704.3 155025.9 0.455 0.095 0.385 0.070 140738.5 168711.9 11.26 6.22 40.8952 15.6190 967.04 7810.94 . 0.050 0.21485 0.14284 0.0160 0.01 0.5684 0.5624 1384.8 42053 140738.5 146070.9 0.000000 0.13420 0.000000 0.15500 1518.4 8519 0.24735 0.09348 221.4 4039 0.044319 0.036067 297.2 1574.0 2.03711 1.80489 0 0.22172 0.34329 0.10288 -0.0105 0.7960 14.9420 4.4133 22.9268 1.60281 0.13008 0.09363 0.10500 0.02704 466.71 -1277.84 0.32552 -0.19559 1.03534 0.15535 0.16121 0.25739 6138.6 91134 27156 1459.8 460.48 97.15 0.0 22641 2531693 4432500 9318.6 89596 2119.8 3225.9 66.392 45.2807 9318.6 89596 1.05 1.05
4 2006-12-31 1.21 1.63 0.3342 0.1988 0.000000 0.44312 18473.4 103234 17039.8 28506 1902.8 4362 0.05155 0.06135 1.06026 0.11657 0.60865 0.10323 11243.9 10657 -202.355 -146.379 0.45482 0.76459 74.173 16.0391 571.9 4983 0 0.015960 0 2410.40 2602.68 8923.93 19.5077 1.78345 12.8007 3.39344 58.275 12.9189 50.1898 9.2621 35.694 4.02274 13.8169 1.69529 3550.00 4121.90 12621.00 17604.00 . . 0.26685 0.29293 206877.5 163049.9 0.325 0.130 0.285 0.090 218121.4 173706.9 16.06 7.64 40.9091 13.2369 1474.14 9691.31 . 0.055 0.26197 0.15550 0.0090 0.01 0.6124 0.6406 5795.9 40532 218121.4 151023.9 0.000000 0.13058 0.000000 0.15019 2940.7 9416 0.27730 0.10299 491.3 2815 0.030791 0.046328 310.3 1506.5 2.28226 1.23377 0 0.25599 0.33475 0.13805 0.0481 0.6549 12.8007 5.2980 20.5680 1.65191 0.13988 0.12966 0.14584 0.10242 1128.55 -767.38 0.43361 -0.08599 0.44906 0.22017 0.17258 0.33032 10604.9 91424 26366 2829.9 657.00 101.13 0.0 22683 5350543 11019149 14972.8 96178 3889.6 9860.2 56.078 15.3165 14972.8 96178 0.95 0.95
5 2007-12-31 1.32 1.05 0.4285 0.2235 0.000000 0.55337 25335.8 120431 22689.7 28470 2402.8 4630 0.14919 0.11256 0.85685 0.16344 0.56121 0.13407 14218.6 16146 -269.870 -336.333 0.28005 0.64430 22.671 10.3835 807.7 5201 0 0.025610 0 2770.40 3766.93 10983.90 4.8864 1.28868 4.2003 2.25040 15.948 8.3335 13.7617 6.2169 9.572 2.67455 3.8758 1.20678 5084.40 5892.10 15276.00 20477.00 . . 0.25912 0.28097 81085.5 127303.2 0.230 0.145 0.220 0.110 95304.1 143449.2 18.87 9.42 16.3037 8.9342 2441.70 11891.24 . 0.075 0.48656 0.30649 0.0100 0.01 0.5995 0.5995 8471.1 47598 95304.1 108175.2 0.000000 0.24590 0.000000 0.32608 4203.7 10418 0.25333 0.10546 1034.9 4956 0.050169 0.062366 313.3 1385.2 0.98572 0.71610 0 0.26592 0.30640 0.15464 0.1751 0.5434 4.2003 3.7996 5.7433 1.09505 0.12523 0.15943 0.13611 0.09076 1325.23 -907.33 0.35181 -0.08261 0.44468 0.23076 0.18527 0.36593 16594.0 98786 26453 4860.5 307.65 84.16 0.0 35274 2831538 6537607 20921.2 105490 4804.8 11859.0 19.835 9.1218 20921.2 105490 0.75 0.90
6 2008-12-31 0.88 0.73 0.4044 0.2266 0.000000 0.71587 31767.6 109524 28238.9 13465 2358.5 4171 0.08174 0.06306 0.72702 0.12455 0.49880 0.11785 15845.8 12907 82.169 -382.777 0.35608 0.63548 36.583 13.8453 1212.2 5450 0 0.017700 0 3022.60 6004.02 11760.65 8.1676 1.85069 6.8651 4.31927 26.842 12.0333 22.6941 8.9671 14.364 5.56163 7.8499 2.00755 6632.00 7844.20 15938.00 21388.00 . . 0.09469 0.26210 178017.0 191787.3 0.160 0.105 0.135 0.070 193862.8 204694.3 21.52 10.18 28.8095 12.8585 4775.55 13422.43 . 0.120 0.37771 0.23806 0.0100 0.01 0.6210 0.6032 12393.1 34484 193862.8 170768.3 0.000000 0.16574 0.000000 0.19867 5299.3 12334 0.24314 0.11902 2030.3 4899 0.047274 0.093152 315.1 1339.1 2.28642 1.31860 0 0.24506 0.30428 0.15380 0.0852 0.3058 6.8651 12.6824 8.8946 1.64787 0.13061 0.16475 0.19269 0.18372 1228.47 -1661.78 0.20461 -0.14130 0.48446 0.34105 0.18766 0.91600 21795.6 103630 29723 6542.1 619.98 130.90 0.0 33926 3434825 8080103 22677.7 95533 6034.9 13038.0 32.124 13.0977 22677.7 95533 0.90 0.90
7 2009-12-31 0.98 0.69 0.4180 0.2214 0.000000 0.53423 40496.8 109022 36004.2 22755 809.9 3447 0.12667 0.06737 1.03527 0.14593 0.60461 0.12818 24484.8 13974 319.377 75.923 0.53828 0.80000 29.644 13.5063 1240.0 4994 0 0.018500 0 3354.47 6466.81 12592.34 7.1376 2.02018 5.3686 4.24568 20.308 11.3706 17.6722 8.7903 14.654 5.54596 6.1263 1.97229 8312.20 9552.20 17013.00 22007.00 0.22202 0.25984 0.22201 0.25984 168808.2 193448.6 0.165 0.130 0.125 0.065 193293.0 207422.6 28.41 12.12 20.9071 12.1089 6577.53 14063.42 0.845 0.145 0.18527 0.11964 0.0100 0.01 0.6024 0.5886 11519.4 34881 193293.0 181322.6 0.000000 0.12583 0.000000 0.14394 6520.4 13425 0.27570 0.14020 1934.7 3127 0.032655 0.081803 317.8 1305.3 1.79663 1.03895 0 0.24987 0.35146 0.17767 -0.0521 0.0760 5.3686 7.9685 8.1729 1.89355 0.12993 0.11963 0.20824 0.14020 -110.72 -1471.08 -0.01712 -0.11682 0.56138 0.36101 0.18110 0.58998 23650.6 95758 26772 6494.3 593.97 146.76 0.0 26100 2764989 5406125 27554.8 98083 7936.0 14389.0 24.356 12.6015 27554.8 98083 0.90 0.85
8 2010-12-31 0.85 0.40 0.2957 0.1460 0.069710 0.55263 57851.0 113452 46241.0 23172 4018.0 4185 0.16869 0.04791 1.19283 0.11666 0.60457 0.10270 34975.0 11651 12.300 176.586 0.76091 0.80000 23.971 14.4647 1067.0 4831 0 0.017000 0 3647.45 8178.05 13650.07 5.8784 2.31830 4.1712 4.69495 16.603 12.7564 15.0559 10.0748 11.700 5.76731 4.8198 2.17504 10381.00 11448.00 18150.00 22981.00 0.21221 0.24793 0.21221 0.24793 172359.8 231528.6 0.200 0.120 0.170 0.090 207334.8 243179.6 38.80 13.89 16.6469 13.2383 5214.75 14119.48 0.550 0.160 0.15516 0.14358 0.0100 0.01 0.6309 0.6010 14731.0 40145 203869.8 214555.6 0.016712 0.11771 0.016996 0.13341 8505.0 14833 0.29007 0.14852 56.0 2681 0.026845 0.001910 321.3 1228.0 1.19853 1.20540 0 0.24590 0.35405 0.18174 0.1086 0.2261 4.4089 9.2593 6.9530 2.14835 0.12425 0.13412 0.25230 0.12619 2963.30 -469.41 0.36235 -0.03439 0.55516 0.34002 0.18393 0.64013 29321.0 99870 27863 8523.0 645.90 183.88 3465.0 28624 3018131 5608725 35761.0 106448 10075.0 15623.0 20.235 13.7333 35761.0 106448 0.90 0.85
9 2011-12-31 1.23 0.63 0.3073 0.1459 0.067400 0.60750 72574.0 116433 58145.0 20236 3438.0 4108 0.18890 0.04790 1.17730 0.11150 0.61490 0.10240 44626.0 11922 111.130 -111.350 0.80000 0.80000 23.830 13.7300 1396.0 4815 0 0.019000 0 4129.36 9275.71 15313.70 5.0600 2.22000 3.7900 4.83000 16.320 11.6900 14.5900 9.4500 10.810 5.98000 4.0300 2.27000 11742.00 13138.00 20286.00 25101.00 0.21000 0.24510 0.21000 0.24510 191631.0 237147.2 0.155 0.095 0.185 0.065 236257.0 249069.2 39.20 15.07 18.0500 12.7100 7122.58 16132.05 0.365 0.165 0.16370 0.08850 0.0100 0.01 0.8362 0.5817 17723.0 39634 232053.0 217749.2 0.017800 0.12570 0.018100 0.14380 9737.0 15855 0.25690 0.14830 437.0 5346 0.050000 0.011500 325.1 1163.2 1.54000 1.45000 0 0.25400 0.30980 0.18970 0.0700 0.3350 3.9900 10.7600 6.1200 2.04000 0.11920 0.13230 0.25430 0.12300 2153.13 -818.35 0.23210 -0.05340 0.52340 0.38640 0.16750 0.78350 37905.0 106916 29852 12975.0 707.38 191.55 4204.0 31320 1992857 4551170 47544.0 104689 10517.0 16260.0 22.060 13.3900 47544.0 104689 0.90 0.85



The results before using the %test macro are shown in FigureĀ 50.2, but you can use the test macro to generate a different order of the results. In Output 50.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 50.3.1.