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.