* Code for importing Datastream 900B data;
%let path=C:\Documents and Settings\rogerloh\My Documents\Research Data\paradigm\;

libname pot "&path.pot\";
libname ds "&path.pot\datastream";

*---------------------;
*Step 1 - import all varaibles as text. This preserves the dscodes and NAs, 
	also the getnames=no will name the variables var1-varN, making it eaiser to 
	specify the proc transpose variables later;

PROC IMPORT datafile= "&path\pot\datastream\p.csv"
			OUT=dailyret1
            DBMS=dlm REPLACE;
    		delimiter =',';
			getnames=no; 
			datarow=2;
RUN;

*---------------------;
*Step 2 - Delete redundant rows, in this case the second row
and get the total number of variables as a column i;
data dailyret2;
set dailyret1;
if var1 = 'Currency' then delete;
array all{*} _all_;
i=dim(all);
run;
*save total number of variables into macro j;
proc sql noprint; select distinct i into :j from dailyret2;
*---------------------;

*Step 3a - transpose data variable 1 to variable j,
first into the main table of all returns information;
proc sort data = dailyret2; by var1; run;
proc transpose data=dailyret2 out=dailyret3;
var var2-var%left(&j);
by var1;
where var1 NE 'Code';
run;

*Step 3b - transpose data, next into the legend table which will tell me which dates 
the var_j refers to;
proc transpose data=dailyret2 out=dailyret4;
var var2-var%left(&j);
by var1; where Var1='Code';
run;

*---------------------;
*Step 4- Merge the tables to get the final table with 3 columns- Dscode, Date, and RI;
proc sql;
	create table dailyret5 as
		select substr(b.col1,1,6) as dscode, 
			a.var1 as date, a.col1 as ri
	from dailyret3 a inner join dailyret4 b
		on a._name_=b._name_ 
	order by dscode, date;
quit;

*---------------------;
*Step 5 - Finally, change the formats of the final table;
data dailyretfinal;
	set dailyret5;
	returnindex= input(RI,best10.);
	DSdate=input(date,mmddyy10.); format dsdate mmddyy10.;
	Keep Dsdate dscode returnindex;run;
proc sort data = dailyretfinal;
	by dscode dsdate;
run;
*End Code ---------------------;