ACS Data Downloaded From the Census Bureau Into an Excel File Hard to Work With

[terryreid314]

Hello my name is Terry Reid and I am enrolled in a beginner's level SAS class. I am using the current version of SAS Studio. I am having serious trouble downloading census data and putting it in a usable format in a table for SAS. It comes out looking like three rows instead of individual columns with the headings that I searched on. I have reviewed the SAS video Creating a SAS Table from CSV File multiple times. If you notice the file they show is already formatted in easy to recognize rows with multiple columns with the variables at the top. The printout that I downloaded from the census, which is attached to this request, has multiple variables and data in 3 rows without clearly separating the variables into individual columns with the data properly presented below it in rows associated with those variables. I need to learn the steps for properly downloading a file from the Census Bureau or how to make it usable once I get it, if this is the only format available.

In addition, I need to learn how to code for several counties from different states, where each county will be presented independently or collectively in a report. An example of the variables are Earnings Range Low, Earnings Range High, Estimated Population, Date, County, State, Gender, Worked Full-Time and Worked Full Time for 12 Months. Most of these variables are in the report mentioned above, but not in an easily usable format. 

Thank you for any help that you can provide.

[Reeza]

Please attach a portion of the file and show what code you've used to try and import the data set.

[Cynthia_sas]

Hi: It looks here https://www.census.gov/programs-surveys/acs/data/summary-file.html like Census has some sample SAS programs on the documentation page:

Is that what you're using?
Cynthia

[terryreid314]

Actually I've used this link https://factfinder.census.gov/faces/nav/jsf/pages/searchresults.xhtml?refresh=t

Both screens are part of the US Census Bureau. I'm not sure if there is a difference in how they present their reports.

[Reeza]

I believe the link Cynthia supplied is more geared toward providing data for research and are machine readable. The files from your link are designed for presentation and clarity.
Since you want a machine readable file, the other source may be easier to work with.

[terryreid314]

Thank you, I'm reviewing that now.

[terryreid314]

I don't know how to ask for the machine readable version. I simply indicated that I wanted data and I was routed to the link that I identified above.

[terryreid314]

I was hoping someone would give me the step by step directions to download the data I mentioned above in the most user friendly form. I'm also interested in getting feedback on the second part of my question concerning coding for multiple counties of the  data.

[Reeza]

Census team has a support desk, call them.

Contact on last page
https://www2.census.gov/programs-surveys/acs/summary_file/2017/documentation/tech_docs/How_to_Access...

[ballardw]

The stuff I work with from the Census site usually involves finding the data, data loading data and if there is program to read it that as well, though I often write my own from the descriptions.

Downloading reports and turning them into data is often a headache as reports are intended for people to read, not computers. So formatting that is helpful for people is obnoxious to read.

It might help to describe what options you used to "download" the data as well. Some work better than others.

[terryreid314]

Thank you for your input and question. I identify that I want the sex, gender, earnings and 12 month employment data for people in Guilford County, North Carolina and follow the prompts for downloading it.

[Tom]

Did you LOOK at the file you downloaded?  It does not look hard to read.  It has three lines of data.  

The first line appears to be variable ids.

The second line descriptions.

The third line has the actual values.

Here is a simple way to read it so you can have the values in SAS datasets where you could begin to figure out how to convert it to a format you need.

data ids ;
  infile "&path\&fname" dsd firstobs=1 obs=1;
  col+1;
  length  name $32 id $50 ;
  input id @@;
  name = translate(strip(id),'____',' -.:');
run;

data labels ;
  infile "&path\&fname" dsd firstobs=2 obs=2;
  col+1;
  length label $200 ;
  input label @@;
run;

data values ;
  infile "&path\&fname" dsd firstobs=3;
  col+1;
  length nvalue 8 value $50 ;
  input value @@;
  nvalue = input(value,??comma32.);
run;

data tall;
  merge ids values labels ;
  by col;
run;

For example you could turn it into a wide format again using TRANSPOSE.

proc transpose data=tall out=ids ;
where col < 4 ;
id name;
var value ;
run;
proc transpose data=tall out=numbers ;
where col > 3;
id name ;
var nvalue ;
run;
data want ;
  merge ids numbers;
run;

[terryreid314]

Tom, thank you very much for that code. The other part of my question has not been answered. So, I will put the code here for those who understand what I'm missing. In its current form it will give me output of 5 different counties, but they appear as 5 different pull down options in SAS Studio. Is there any code that I can add that will make all  counties output and print as one continuous document?

%web_drop_table(WORK.GUILFORD);

FILENAME REFFILE '/home/terryreid3140/Guildford_County(TR).xlsx';

PROC IMPORT DATAFILE=REFFILE

DBMS=XLSX

OUT=WORK.GUILFORD;

GETNAMES=YES;

RUN;

PROC CONTENTS DATA=WORK.GUILFORD; RUN;

%web_open_table(WORK.GUILFORD);

%web_drop_table(WORK.MARICOPA);

FILENAME REFFILE '/home/terryreid3140/Maricopa_County(TR).xlsx';

PROC IMPORT DATAFILE=REFFILE

DBMS=XLSX

OUT=WORK.MARICOPA;

GETNAMES=YES;

RUN;

PROC CONTENTS DATA=WORK.MARICOPA; RUN;

%web_open_table(WORK.MARICOPA);

%web_drop_table(WORK.OKALOOSA);

FILENAME REFFILE '/home/terryreid3140/Okaloosa_County (TR).xls';

PROC IMPORT DATAFILE=REFFILE

DBMS=XLS

OUT=WORK.OKALOOSA;

GETNAMES=YES;

RUN;

PROC CONTENTS DATA=WORK.OKALOOSA; RUN;

%web_open_table(WORK.OKALOOSA);

%web_drop_table(WORK.ORANGE);

FILENAME REFFILE '/home/terryreid3140/Orange_County(TR).xlsx';

PROC IMPORT DATAFILE=REFFILE

DBMS=XLSX

OUT=WORK.ORANGE;

GETNAMES=YES;

RUN;

PROC CONTENTS DATA=WORK.ORANGE; RUN;

%web_open_table(WORK.ORANGE);

%web_drop_table(WORK.TUCSON);

FILENAME REFFILE '/home/terryreid3140/Tucson_County(TR).xls';

PROC IMPORT DATAFILE=REFFILE

DBMS=XLS

OUT=WORK.TUCSON;

GETNAMES=YES;

RUN;

PROC CONTENTS DATA=WORK.TUCSON; RUN;

%web_open_table(WORK.TUCSON);

[Reeza · Accepted solution]

It looks like you downloaded each data individually. Assuming the files are exactly the same (unlikely) you can do the following:

data want;
set guilford maricopa okaloosa orange tucson indsname=source;
fname=source;
run;

Check the want data set. If you get an error about mismatch types (highly likely) that means that some variables are numeric in one data set and character in another. You'll need to identify the variables and determine what they should be in the data sets and then fix them before stacking them together. 

---

@terryreid314 wrote:

Tom, thank you very much for that code. The other part of my question has not been answered. So, I will put the code here for those who understand what I'm missing. In its current form it will give me output of 5 different counties, but they appear as 5 different pull down options in SAS Studio. Is there any code that I can add that will make all  counties output and print as one continuous document?

%web_drop_table(WORK.GUILFORD);

FILENAME REFFILE '/home/terryreid3140/Guildford_County(TR).xlsx';

PROC IMPORT DATAFILE=REFFILE

DBMS=XLSX

OUT=WORK.GUILFORD;

GETNAMES=YES;

RUN;

PROC CONTENTS DATA=WORK.GUILFORD; RUN;

%web_open_table(WORK.GUILFORD);

%web_drop_table(WORK.MARICOPA);

FILENAME REFFILE '/home/terryreid3140/Maricopa_County(TR).xlsx';

PROC IMPORT DATAFILE=REFFILE

DBMS=XLSX

OUT=WORK.MARICOPA;

GETNAMES=YES;

RUN;

PROC CONTENTS DATA=WORK.MARICOPA; RUN;

%web_open_table(WORK.MARICOPA);

%web_drop_table(WORK.OKALOOSA);

FILENAME REFFILE '/home/terryreid3140/Okaloosa_County (TR).xls';

PROC IMPORT DATAFILE=REFFILE

DBMS=XLS

OUT=WORK.OKALOOSA;

GETNAMES=YES;

RUN;

PROC CONTENTS DATA=WORK.OKALOOSA; RUN;

%web_open_table(WORK.OKALOOSA);

%web_drop_table(WORK.ORANGE);

FILENAME REFFILE '/home/terryreid3140/Orange_County(TR).xlsx';

PROC IMPORT DATAFILE=REFFILE

DBMS=XLSX

OUT=WORK.ORANGE;

GETNAMES=YES;

RUN;

PROC CONTENTS DATA=WORK.ORANGE; RUN;

%web_open_table(WORK.ORANGE);

%web_drop_table(WORK.TUCSON);

FILENAME REFFILE '/home/terryreid3140/Tucson_County(TR).xls';

PROC IMPORT DATAFILE=REFFILE

DBMS=XLS

OUT=WORK.TUCSON;

GETNAMES=YES;

RUN;

PROC CONTENTS DATA=WORK.TUCSON; RUN;

%web_open_table(WORK.TUCSON);

---