clear
set more off
#delimit;
set segmentsize 32m;
set min_memory 800m;

/*************************************************************************************************************
This do-file reads in the NIPA tables. The data are downloaded in CSV format from the BEA's Advance Download section: http://bea.gov//national/nipaweb/nipa_underlying/DownSS2.asp 

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

capture run paths;
tempfile data;
qui gen line=.;
qui save `data', replace;
/*************************************************************************************************************
1. NIPA data
*************************************************************************************************************/
foreach s in 0 2 3 4 5 7 {;
	qui insheet using "http://bea.gov/national/nipaweb/nipa_underlying/ss_data/section`s'all_csv.csv", comma nonames clear double;
	qui describe, short;
	local no_vars = r(k); *Number of variables in dataset;

	**Table descriptive variables;
	qui gen table = v1 if substr(v1,1,5)=="Table"; *Table number variable;
	qui gen units = v1 if substr(v1[_n-1],1,5)=="Table"; *Units variable;
	qui replace units = "n.a." if substr(v1[_n-1],1,5)=="Table" & units==""; *E.g. contributions to percent change in GDP;
	qui egen periodicity = ends(v1) if substr(v1[_n-2],1,5)=="Table", punct(" ") head; *Periodicity variable;
	qui egen vintage = ends(v1) if substr(v1[_n-4],1,5)=="Table", punct("published") last trim; *Data vintage;
	foreach var of varlist table units periodicity vintage {;
		qui replace `var' = `var'[_n-1] if `var'=="";
	 };

	**Line descriptive variables;
	qui replace v1 = "0" if v1=="Line"; *This is the line with the year;
	qui replace v1 = "0.5" if v1=="" & v1[_n-1]=="0"; *This is the variable with the quarter/month if applicable;
	qui destring v1, force replace;
	rename v1 line;
	qui keep if v4!="";
	rename v2 description;
	rename v3 code;
	qui compress;

	**Reshape into panel, table-by-table;
 	qui egen tableid = group(table periodicity v4) if line==0; *Numeric identifier for each NIPA table or table component in the section. The monthly tables "wrap." v4 picks up the first year;
	qui replace tableid = tableid[_n-1] if missing(tableid);
	local no_tables = tableid[_N];
	forvalues i = 1/`no_tables' {;
		preserve;
		qui keep if tableid==`i';
		disp(table[1]);
		forvalues t = 4/`no_vars' {; *Rename variables to valueyyyy[_Q/M];
			qui tostring v`t', force replace; *Some empty columns are read in as numeric;
			if v`t'[1]=="" | v`t'[1]=="." {; *Variable has less than the maximum number of observations in the section;
				qui drop v`t';
				continue;
			};
			local yyyy = v`t'[1];
			rename v`t' value`yyyy';
			if line[2]==0.5 {; *Table is quarterly or monthly;
				local qm = value`yyyy'[2];
				rename value`yyyy' value`yyyy'_`qm';
			};
		};
		qui drop in 1;
		qui reshape long value, i(table units periodicity vintage description code line) j(datestring) string;
		qui append using `data';
		qui save `data', replace;
		restore;
	};
};

qui use `data', replace;	
qui destring value, force replace ignore(",");
qui drop if line==0.5;

qui split table, limit(2);
qui egen table_number = concat(table1 table2), punct(" ");
qui gen table_name = subinstr(table,table_number,"",.);
qui drop table table1 table2;

qui gen date = date(datestring,"Y") if periodicity=="Annual";
qui replace date = dofq(quarterly(datestring,"YQ")) if periodicity=="Quarterly";
qui replace date = dofm(monthly(datestring,"YM")) if periodicity=="Monthly";
qui format date %td;

foreach var of varlist table_name periodicity units desc {;
	qui replace `var' = subinstr(`var',"  ","",.);
};
qui compress;

qui sort table_number periodicity line date;	
	
order table_number table_name units vintage code description line date value;

qui save NIPA-underlying-detail, replace;
qui gzipfile NIPA-underlying-detail.dta, saving("$SavePath/NIPA-underlying-detail", replace);
qui erase NIPA-underlying-detail.dta;