/************************************************************************************************************************/
/* Program Name: Example Program for Summary File Prototype (Basic)					                  					*/
/* Version 1, Jun 17 2020                                                                             					*/      
/* Output: Data for any given 1-year table for all geographies and sub-geographies of the given state 					*/
/* ACS Summary File Website: 																							*/
/*		https://www.census.gov/programs-surveys/acs/technical-documentation/summary-file-documentation.html  			*/
/* Program Documentation:																								*/
/*		https://www2.census.gov/programs-surveys/acs/summary_file/2018/prototype/program/documentation					*/  
/************************************************************************************************************************/


/*------------ADD INPUT HERE ------------*/
%let Table_ID =b19001;
%let State  =ca;
%let Data_Dir =../../1YRData;
%let Geo_File =../../20181YRGeos.csv;
/*---------------------------------------*/

libname out ".";

/** Import Data **/
proc import datafile = "&Data_Dir./acsdt1y2018-&Table_ID..dat"
  out = out.&Table_ID
  dbms = dlm
  replace;
  getnames = yes;
  delimiter = '|';
  GUESSINGROWS=10000;
run;

/* import geography labels */
proc import datafile="&Geo_File"
    out=Geos
    dbms=csv
    replace;
    getnames=yes;
	GUESSINGROWS=10000;
run;

/* remove geo id vintage code in data 
(required to match data with geography labels) */
data out.&Table_ID;
	length part1 $3.;
	length part2 $37.;
	set out.&Table_ID;
		_geo_id=trim(_geo_id);
		part1=substr(_geo_id,1,3);
		part2=substr(_geo_id,6);
		_geo_id=compress(part1||part2);
	drop part1 part2 ;
run;

/* merge data with geography labels and output */
proc sql;
	create table out.&Table_ID as 
	select geo.name, tbl.*
    from out.&Table_ID as tbl 
	left join Geos as geo
	on tbl._geo_id =geo.geoid
	where geo.stusab = upcase("&State");
quit;