Tag: census

Tableau: Load Census Shape Data

A. Download data from web

  • 2016 Census Boundary File
  • select ArcGIS (.shp)
  • select census subdivisions and continue to download
  • unzip file to local directory

B. Tableau

  • Data-> New Data Source ->Spatial File-> Open the .shp file
  • Open a new sheet, drag Geometry from Measures to the canvas
  • drag Ccsname over the Colour Property in Marks card
Coloured by census consolidated subdivision name

C. Attribute

  • FID: specific to ArcGIS
  • Shape: specific to ArcGIS
  • CCSUID: Uniquely identifies a census consolidated subdivision (2-digit province/territory + 2-digit census division code +3-digit census consolidated subdivision code)
  • CSDUID: Uniquely identifies a census subdivision (2-digit province/territory + 2-digit census division code +3-digit census subdivision code)
  • CCSNAME: Census consolidated subdivision name.
  • CSDNAME: Census subdivision name.
  • PRUID: Uniquely identifies a province or territory.
  • PRNAME: Province or territory name.
  • CDUID: Uniquely identifies a census division.
  • CDNAME: Census division name.
  • CDTYPE: Census division type.
  • CSDTYPE: Census subdivision type.
  • ERUID: Uniquely identifies an economic region ( 2-digit province/territory + 2-digit economic region code
  • ERNAME: Economic region name.
  • SACCODE: 3-digit statistical area classification code
  • SACTYPE: The statistical area classification groups census subdivisions according to whether they are a component of a census metropolitan area, a census agglomeration, a census metropolitan influenced zone or the territories.
  • CMAUID: Uniquely identifies a census metropolitan area/census agglomeration.
  • CMAPUID: Uniquely identifies the provincial/territorial part of a census metropolitan area/census agglomeration.
  • CMANAME: Census metropolitan area/census agglomeration name.
  • CMATYPE: Identify whether the unit is a census metropolitan area, a tracted census agglomeration or a non-tracted census agglomeration.

SAS: Reading Census Data

Census data is exceptionally large. The 2016 census profile for Ontario is 4.5G and has more than 46,694,909 lines of records. To extract the data efficiently, StatsCan provides a csv file that identifies the starting row number for each geography. Using this file, you can compile the parameter list for the geographical area of interest at the selected geographic level, eg. province level, census division level, and census subdivision level.
Census file can be downloaded at link
Step 1: Compile parameter lists

%put &name.;
Canada Ontario Durham York Toronto Peel Halton
 %put &start.;
2 2249 7287023 9513800 12198965 20521853 25289987
 %put &end.;
2248 4495 7289269 9516046 12201211 20524099 25292233

Step 2: Extract and Compile data

%macro ext (namelst=, startlst=, endlst=);
proc datasets library=work noprint;
delete census;
quit;
%let i=1;
%do %while (%scan(&namelst., &i, ' ') ne );
%let parm1=%scan(&namelst., &i, ' ');
%let parm2=%scan(&startlst., &i, ' ');
%let parm3=%scan(&endlst., &i, ' ');
data census_&parm1.;
infile 'X:\Work\Stats Can\98-401-X2016044_ONTARIO_eng_CSV\98-401-X2016044_ONTARIO_English_CSV_data.csv'
delimiter = ',' firstobs=&parm2. obs=&parm3. TRUNCOVER  DSD LRECL=32767 ;
INFORMAT 
year 8.
geo_code  $13.
geo_level 8.
geo_name $80.
gnr 8.1
gnr_lf 8.1
quality_flag $5. 
alt_geo_code 8.
Item $50.
itemID 8.
Notes 8.
Total 8.
Male $8.
Female $8.
;
FORMAT 
year 8.
geo_code  $13.
geo_level 8.
geo_name $50.
gnr 8.1
gnr_lf 8.1
quality_flag $5. 
alt_geo_code 8.
Item $80.
itemID 8.
Notes 8.
Total 8.
Male $8.
Female $8.
;
input
year 
geo_code $
geo_level 
geo_name $
gnr
gnr_lf
quality_flag 
alt_geo_code 
Item $
itemID 
Notes 
Total
Male $
Female $
;
run;
proc append base = census data = census_&parm1.;
run;
%let i = %eval (&i +1);
%end;
%mend ext;
%ext (namelst=&name., startlst=&start., endlst=&end. );

The resulting dataset is only 4.1mb, which you can manipulate efficiently.