/*****************************************************************************************************************
Instructions to run program:
   1. enter a VALID table ID on line 24--
           a.  Table must be in the dataset table_shells.sas7bdat on http://www2.census.gov/EEO_2006_2010 (use the 
               TABLE_ID variable)
           b.  Examples are eeoall1r, eeoall1w, and eeoall7wn1

   2. Save http://www2.census.gov/EEO_2006_2010/EEO_2006_2010_Tables_By_Table_Set/eeo-10_5yr-<table number>.zip to a 
      folder (directory) you can access

   3. Put this folder name on line 27

   4. Save the data sets eeo_geographic_file.sas7bdat,eeo_geographic_flows, and table_shells.sas7bdat in 
      http://www2.census.gov/EEO_2006_2010 to a folder

   5. Put this folder name on line 30

   6. Change the file locations ("out_dir") in line 33

Questions?  Please email Ted Logothetti at theodore.c.logothetti@census.gov
******************************************************************************************************************/

/*PUT IN TABLE NUMBER HERE*/
%let tblid=EEOall1W;

/*PUT IN FOLDER NAME WHERE YOU SAVED http://www2.census.gov/EEO_2006_2010/EEO_2006_2010_Tables_By_Table_Set/eeo-10_5yr-<table number>.zip*/
%let directory=h:\temp\eeo;

/*PUT IN THE NAME OF THE FOLDER WHERE YOU SAVED eeo_geographic_file.sas7bdat,eeo_geographic_flows, and table_shells.sas7bdat */
libname dict 'h:\temp';

/*PUT IN FOLDER WHERE YOU WANT THE OUTPUT DATA SET*/
libname out_dir 'h:\temp\eeo';

/*RUN PROGRAM*/

options symbolgen missing='';

data _null_;
  length next $ 1 number_and_letter $ 3;
  tblid=lowcase("&tblid");
  if tblid in('eeoall1r','eeoall1w','eeoall13r','eeoall14r','eeoall8w')then
    call symputx('zip_file',cats('eeo-10_5yr-',tblid,'.zip'));
  else do;
    number=substr(tblid,anydigit(tblid),1);
	next=substr(tblid,anydigit(tblid)+1,1);
	if next in ('w','r') then number_and_letter=cats(number,next);
	else number_and_letter=cats(number,next,substr(tblid,anydigit(tblid)+2,1));
    containing_file='eeoall'||number_and_letter;
	put containing_file=;
    call symputx('zip_file',cats('eeo-10_5yr-',containing_file,'.zip'));
  end;
  call symputx('dat_file',cats('eeo-10_5yr-',lowcase("&tblid"),'.dat'));
run;

data _null_;
  call symputx('path',cats("&directory",'\',"&zip_file"));
run;

filename zip_file Saszipam "&path";
%put &path;

data _null_;
  length table_number $ 2;
  set dict.table_shells end=last;
  where table_id=upcase("&tblid");
  array columns{16} $ column_1-column_16;
    if line_num='1' then do i=1 to 16;
    if columns[i] ne '' then column_number+1;
	if i=16 then call symputx('columns',cats(2*column_number));
  end;

  if _n_=1 then do;
  call symputx('table_title',stub);
  table_number=substr(table_id,anydigit(table_id),2);
  if table_number in('1W','2W','3W','4W','5W','6W','7W') then flow='YES';
  if  flow='YES' and find(upcase(stub),'OCCUPATION') then do;
     call symput('other_vars','flowid flow_area occ ');
     call symput('other_len','flowid $ 14 occ $ 4');
     call symput('other_input','flowid $ occ $ ');
     call symput('other_label','flowid="Flow ID" occ="Occupation" flow_area="Flow Area"');
  end;
  else if flow='YES' then do;
     call  symput('other_vars','flowid flow_area ');
     call symput('other_len','flowid $ 14 ');
     call symput('other_input','flowid $  ');
     call symput('other_label','flowid="Flow ID" flow_area="Flow_Area"');
  end;
  else if find(upcase(stub),'OCCUPATION') then do;
     call  symput('other_vars','occ ');
     call symput('other_len','occ $ 20 ');
     call symput('other_input','occ $  ');
     call symput('other_label','occ="Occupation"');
  end;
  else do;
     call  symput('other_vars','');
     call symput('other_len','');
     call symput('other_input','');
     call symput('other_label','');
  end;  
end;
    do i=1 to 16;
    if strip(columns[i]) in('#','%') then cell_total+1;
  end;
  if last then do;
  call symputx('cells',cats(cell_total));
  end;
run;

data cntrl (keep=stub line_num rename=(stub=title line_num=lineno));
  attrib variable_label1 variable_label2 missing length=$30000;
  retain variable_label1-variable_label2 missing;
  set dict.table_shells end=last;
  array cols{*} column_1-column_16;
  where table_id=upcase("&tblid");
  nprofln=input(line_num,best12.);
  if column_1 not='' & column_2 not='' & nprofln<1 then do;
   do i=1 to &columns ;
     if mod(i,2)=1 then variable_label1=cat(strip(variable_label1)," v",i,"='Estimate: ",cols[ceil(i/2)],"'");
     else variable_label2=cat(strip(variable_label2)," v",i,"='Margin of Error: ",cols[ceil(i/2)],"'");
     if i<&columns then missing=cats(missing,'v',i,',');
     else do;
         missing=cats(missing,'v',"&columns");
         call symputx('missing',missing);
     end;
   end;
   call symputx('label1',compbl(variable_label1));
   call symputx('label2',compbl(variable_label2));
 end;
 else do i=1 to &columns/2;
    linetotal+sum(count(cols[i],'#'),count(cols[i],'%'));
    if i=&columns/2 then call symput('cells',cats(2*linetotal));
 end;
run;

data cntrl;
  set cntrl;
  retain type 'n' fmtname 'stubs';
  start=lineno;
  label=title;
run;

proc format cntlin=cntrl;
run;

data _null_;
  length all $ 10000;
  set cntrl;
  where 0<input(lineno,6.) < 1 and title is not missing;
  all='order2='||strip(lineno)||';'||'stub2="'||strip(title)||'";output;order2=.;stub2="";';
  call symputx('start',all);
 run;

data cntrl2;
  set cntrl (drop=fmtname);
  retain fmtname 'insert';
  where input(lineno,6.) between floor(input(lineno,6.))+.7 and floor(input(lineno,6.)) +.9 and input(lineno,6.) ge 1;
run;

proc format cntlin=cntrl2;
run;

data geo;
  set dict.Eeo_geographic_file ;
  retain fmtname 'geo' type 'c';
  rename geoid=start area_name=label;
run;

proc format cntlin=geo;
run;

data flows;
  set dict.Eeo_geographic_flows;
  where flowid ne '0';
  if substr(geoid,10,1)='0' then start='0'||flowid;
  else start=flowid;
  retain fmtname 'flows' type 'c';
  rename area_name=label;
run;

proc format cntlin=flows;
run;

data _null_;
  call symputx('num_rows',cats(&cells/&columns));
run;

data &tblid (label="&table_title" keep=geoid name order2 stub2 &other_vars v1-v&columns rename=(order2=order stub2=stub));
    length geoid $ 20 name $ 50 order order2 8 stub stub2 $ 20 &other_len ;
    infile zip_file(&dat_file) lrecl=50000 dlm='|'  ;
	
    array dat{&cells} a1-a&cells;
        array vars{&columns} v1-v&columns;
        retain v1-v&columns name;
        input geoid &other_input  dat[*];if _n_=1 then put _infile_;
        do i=1 to &cells;
        j=mod(i,&columns);
        if j=0 then do;
            j=&columns;
            row+1;
        end;
        vars[j]=dat[i];
        order=mod(row,&num_rows);
        if order=0 then do;
          name=put(geoid,$geo.);
          order=&num_rows;
		   if find("&other_input","flow") then do;
              if flowid ne '0' then do;
               flow_area=put(flowid,$flows.);
			  end;
		   end;
        end;
        stub=put(order,stubs.);
        if j=&columns then do;
           if order=1 then do;
             &start;
           end;
              order2=order;
              stub2=stub;;
           output;
           do k=.7 to .9 by .1;
           stripper=strip(put(order+k,insert.));
           if stripper not=cats(order+k) then do;
             order2=order+k;
             stub2=stripper;
             output;
           end;
           end;

        end;
    end;
    label geoid='Geographic ID' name='Geography' &other_label &label1 &label2;
run;

data out_dir.&tblid;
  set &tblid;
  if round(order,1) not=order then call missing(&missing);
run;