/*************************************************************************************/


/********************************************************************
SAS MACRO: download_single macro 

Developed by Zheng Kuang and Jin Man Lee
Created : 2013. 4. 15
Relased : 2015. 8. 27 
Revision History:
    2015.8.29 : Minor revision in the drop command at the end (No impacts to original work)


data_out:	output dataset name	
symbol:		symbol of security for downloading
st_date:	start date for downloading, default: 01Jan1900
end_date:	end date for downloading, default: today
price_freq:	price freqency for downloading, default: d(daily), 
			other input: w(weekly), m(monthly), v(dividends only)

import Process macro variables:
default_var:	default starting number of variables
default_len:	default starting length of each variable
default_label:	whether input file has label inside dataset
default_over:	option for truncover, missover, or flowover
default_dlm:	delimiter of input file

THIS PROGRAM IS FOR PUBLIC DOMAIN. 
DO NOT USE FOR COMMERCIAL PURPOSE.
IF YOU WANT TO USE FOR COMMERCIAL PURPOSE, PLEASE CONTACT 
JIN MAN LEE (jlee141@depaul.edu) FOR PERMISSION. 

********************************************************************/
%MACRO download_single(data_out, symbol, st_date=01Jan1900, end_date=&sysdate9., 
price_freq=d, default_var=20, default_len=20, default_label=N, default_over=truncover, 
default_dlm='2C0D'x);
data url_gen;
date_start = "&st_date."d;
date_end = "&end_date."d;
m_start = cats('&a=',month(date_start)-1);
d_start = cats('&b=',day(date_start));
y_start = cats('&c=',year(date_start));
m_end = cats('&d=',month(date_end)-1);
d_end = cats('&e=',day(date_end));
y_end = cats('&f=',year(date_end));
link = 
cats('http://ichart.finance.yahoo.com/table.csv?s=',"&symbol.",m_start,d_start,y_start,m_end,d_end,y_end,'&g=',"&price_freq.",'&ignore=.csv');
call symput('link',compress(link,' '));
run;
%put &link.;

filename csv_in url "&link." lrecl=99999999 debug;

data var_name;
array var[&default_var] $&default_len;
%if &default_label=Y %then %do;
	infile csv_in dsd truncover lrecl=99999999 dlm=&default_dlm. firstobs=1 obs=2;
%end;
%if &default_label=N %then %do;
	infile csv_in dsd truncover lrecl=99999999 dlm=&default_dlm. firstobs=1 obs=1;
%end;
input var1 - var&default_var;
run;

%do i = 1 %to &default_var;
	data var_name_1; set var_name; if _N_=1; keep var&i; rename var&i=var_in; run; 
*var_in is variable label;
	%if &default_label=Y %then %do;
		data var_name_2; set var_name; if _N_=2; keep var&i; rename var&i=var_out; 
run; *var_out is variable name;
	%end;
	%if &default_label=N %then %do;
		data var_name_2; set var_name; if _N_=1; keep var&i; rename var&i=var_out; 
run;
	%end;
	data var_name_12; merge var_name_1 var_name_2; 
	var_in = strip(var_in);
	var_out = strip(var_out);
	run;
	%if &i=1 %then %do;
	data var_list; set var_name_12; run;
	%end;
	%else %do;
	data var_list; set var_list var_name_12; run;
	%end;
%end;

data var_list;
set var_list;
if compress(var_out)='' & compress(var_in)=''  then delete;
if compress(var_in)='' then var_in = var_out;
if compress(var_out)='' then var_out = var_in;
var_out = compress(var_out,"-+=.,/'\~!@^&*():;?");
do i = 2 to length(var_out)-1;
	if substr(var_out,i,1)=' ' then 
var_out=substr(var_out,1,i-1)||'_'||substr(var_out,i+1,length(var_out)-i);
	if substr(var_out,i,1)='#' then 
var_out=substr(var_out,1,i-1)||'Num'||substr(var_out,i+1,length(var_out)-i);
	if substr(var_out,i,1)='$' then 
var_out=substr(var_out,1,i-1)||'Dol'||substr(var_out,i+1,length(var_out)-i);
	if substr(var_out,i,1)='%' then 
var_out=substr(var_out,1,i-1)||'Pct'||substr(var_out,i+1,length(var_out)-i);
end;
if substr(var_out,length(var_out),1)='#' then 
var_out=substr(var_out,1,length(var_out)-1)||'Num';
if substr(var_out,1,1)='#' then var_out='Num'||substr(var_out,2,length(var_out)-1);
if substr(var_out,length(var_out),1)='$' then 
var_out=substr(var_out,1,length(var_out)-1)||'Dol';
if substr(var_out,1,1)='$' then var_out='Dol'||substr(var_out,2,length(var_out)-1);
if substr(var_out,length(var_out),1)='%' then 
var_out=substr(var_out,1,length(var_out)-1)||'Pct';
if substr(var_out,1,1)='%' then var_out='Pct'||substr(var_out,2,length(var_out)-1);
if compress(substr(var_out,1,1),"0123456789")='' then var_out="V"||var_out;
*if var_out="DATE_SERVICE_REQUEST_WAS_RECEIVED" then var_out="DATE_SVC_REQ_RECEIVED";
drop i;
format var $10.;
var = cats("var",_N_);
run;

data _NULL_;
set var_list;
call symputx("max_var",_N_);
call symput(cats("varin",_N_), compress(var_in,"'"));
call symput(cats("varout",_N_), var_out);
run;

data ds_1;
array var[&max_var] $&default_len;
%if &default_label=Y %then %do;
	infile csv_in dsd &default_over. lrecl=99999999 dlm=&default_dlm. firstobs=3;
%end;
%if &default_label=N %then %do;
	infile csv_in dsd &default_over. lrecl=99999999 dlm=&default_dlm. firstobs=2;
%end;
input var1 - var&max_var;
%do j = 1 %to &max_var;
	len&j = length(var&j);
%end;
run;

proc means data=ds_1 noprint;
output out=max_len max(len1-len&max_var)=maxlen1-maxlen&max_var; 
run;

%do k = 1 %to &max_var;
	%if &k=1 %then %do;
	data maxlen_list; set max_len; keep maxlen&k; rename maxlen&k=maxlen;
	%end;
	%else %do;
	data maxlen_list; set maxlen_list max_len(keep=maxlen&k rename=(maxlen&k=maxlen)); 
run;
	%end;
%end;

data _NULL_;
set maxlen_list;
format fmt $10.;
fmt = cats("$",maxlen,".");
call symputx(cats("maxlen",_N_), maxlen);
call symput(cats("fmt",_N_), fmt);
run;

data ds_2;
%do i = 1 %to &max_var ;
	format &&varout&i &&fmt&i ;
	length &&varout&i $&&maxlen&i ;
%end;
set ds_1;
%do i = 1 %to &max_var ;
	&symbol.__&&varout&i = var&i ;
	
	label &symbol.__&&varout&i = &&varin&i ;
	drop var&i len&i ;
%end;
&symbol = &symbol.__Adj_close*1 ;
run;

data ds_3;
set ds_2;
day = mdy(substr(&symbol.__date,6,2),substr(&symbol.__date,9,2),substr(&symbol.__date,1,4)) ;
          format day date10. ;
drop date    Open  High  Low   Close Volume Adj_Close  ;
proc sort ; by day ;

data &data_out;
set ds_3; date = day ;  format date date10. ; year = year(date) ; month = month(date) ;
      &symbol._Open =  &symbol.__open*1 ; 
      &symbol._High =  &symbol.__High*1 ; 
      &symbol._Low =   &symbol.__Low*1 ; 
      &symbol._Close =  &symbol.__Close*1 ; 
      &symbol._Vol =  &symbol.__Volume*1 ; 
      &symbol._AdjP =  &symbol.__Adj_close*1 ; 
   drop day  &symbol.__Open  &symbol.__High  &symbol.__Low   &symbol.__Close 
             &symbol.__Volume &symbol.__Adj_Close ; 
run ;

%MEND download_single;
/*************************************************************************************/


/*************************************************************************************/


/**********************************************************************************************
Federal Reserve Economic Data Download and read into SAS 
Created on August 30, 2015
Created by Jin Man Lee


Example to use command :
To read sp500 index from FRED and save the variable name of sp500 :

%read_fred(SP500, sp500, "SP 500 Index");

Note: The first SP500 is the actual file name in FRED website:
https://research.stlouisfed.org/fred2/series/SP500
Thus the first name has to be always CAPITAL LETTER

***********************************************************************************************/

/* Macro for downloading and reading into SAS */

%MACRO read_fred(    url_code,     fred_name ,  	fred_title  );
filename fredcode url "http://research.stlouisfed.org/fred2/data/&url_code..txt";
 
data FRED ;
   infile fredcode  firstobs=2 ;
   format DATE mmddyy10.;
   input  @1 DATE yymmdd10.  @13 &fred_name ;
   label &fred_name =  &fred_title ; 
   if &fred_name = . then delete   ;
run; 
 
filename fredcode ; /* close file reference */
 
 data FRED_DATA ;
    merge   FRED_DATA FRED ;      by DATE ;
    if DATE ; 
    YEAR = year(DATE) ; MONTH = month(DATE) ; 
run;

%MEND ;

/*************************************************************************************/