topic Re: Reading in 5GB birth dataset from https://www.cdc.gov/nchs/data_access/Vitalstatsonline.htm#Down in SAS Programming

Reading in 5GB birth dataset from https://www.cdc.gov/nchs/data_access/Vitalstatsonline.htm#Download

Flexluthorella — Mon, 17 Feb 2020 20:26:38 GMT

I've downloaded the 2018 Birth data files (US data files only) which is supposedly 223 mb. When the download was completed on my pc, its over 5GB. Notepad can't read it so I cant view the dataset/variables. I attempted to PROC IMPORT into SAS but that is not working.

Re: Reading in 5GB birth dataset from https://www.cdc.gov/nchs/data_access/Vitalstatsonline.htm#Down

mkeintz — Mon, 17 Feb 2020 20:49:10 GMT

@Flexluthorella wrote:

I've downloaded the 2018 Birth data files (US data files only) which is supposedly 223 mb. When the download was completed on my pc, its over 5GB. Notepad can't read it so I cant view the dataset/variables. I attempted to PROC IMPORT into SAS but that is not working.

When you say "not working" we have virtually no information to provide advice. Now NOTEPAD finds the downloaded file too big. How about WORDPAD (use it to view, but not save), or you could download many other editors, like Notepad++. These both likely have larger size limitations.

And if you downloaded something sized 223mb and got a 5GB file, it was more that a simple download. Try using a more capable editor to view the download. BTW, what it the url of the downloaded file? Maybe someone on this forum can take a quick look.

Re: Reading in 5GB birth dataset from https://www.cdc.gov/nchs/data_access/Vitalstatsonline.htm#Down

PGStats — Mon, 17 Feb 2020 20:50:47 GMT

Have you tried the tools provided on this NCHS site ?

Re: Reading in 5GB birth dataset from https://www.cdc.gov/nchs/data_access/Vitalstatsonline.htm#Down

Flexluthorella — Mon, 17 Feb 2020 20:56:11 GMT

"Not working" meaning I can't open the file in notepad, notepad++ or wordpad, as they error message states file is "too big" or "failed to open". I am not sure what you mean by more capable editor. The url is https://www.cdc.gov/nchs/data_access/Vitalstatsonline.htm#Tools and its the 2018 Birth data file.

Re: Reading in 5GB birth dataset from https://www.cdc.gov/nchs/data_access/Vitalstatsonline.htm#Down

Flexluthorella — Mon, 17 Feb 2020 20:57:10 GMT

I do not know how to use the tools they provide. I did not think I could just start downloading tools to use with no idea how to use them.

Re: Reading in 5GB birth dataset from https://www.cdc.gov/nchs/data_access/Vitalstatsonline.htm#Down

mkeintz — Mon, 17 Feb 2020 21:13:31 GMT

By more capable editor, I meant more capable than notepad, thinking either wordpad or notepad++ would do the job. But I see you have tried that.

BUT I also see you have unzipped the downloaded file, so you can do this to make a sample file to visually inspect:

filename filein "C:\Users\…..\Downloads\Nat2018us\Nat2018PublicUS.c20190509.r20190717.txt";

data _null_;
  infile filein;
  file 'c:\temp\sampledata.txt';
  input;
  put _infile_;
  if _n_>=10 then stop;
run;

Then take a look at sampledata.txt.

Re: Reading in 5GB birth dataset from https://www.cdc.gov/nchs/data_access/Vitalstatsonline.htm#Down

Flexluthorella — Mon, 17 Feb 2020 21:23:15 GMT

This gives me the first 10 lines of data. The sampledata.txt did not give variable names. I can't tell what I need to do from here. I can see a small fraction of the data.

Re: Reading in 5GB birth dataset from https://www.cdc.gov/nchs/data_access/Vitalstatsonline.htm#Down

mkeintz — Mon, 17 Feb 2020 21:36:04 GMT

If you go back to the url you provided, you will see a column to the left of your downloaded data. The column name is titled "User's Guide (.pdf files)". Clicking on the "2018 (1.7MB)" link in this self-descriptive column will provided a guide to the layout of the data in a pdf file.

This is a common practice with lots of demographic data files - one file with just data, and another file/codebook/user guide with the data layout description. Welcome to the demographic data world.

Re: Reading in 5GB birth dataset from https://www.cdc.gov/nchs/data_access/Vitalstatsonline.htm#Down

Flexluthorella — Mon, 17 Feb 2020 21:57:05 GMT

Right. Going back to my original issue, how do I get to read in ALL the data from the OG (large) file?

Re: Reading in 5GB birth dataset from https://www.cdc.gov/nchs/data_access/Vitalstatsonline.htm#Down

Patrick — Tue, 18 Feb 2020 01:31:01 GMT

The .pdf User Guide provides the data dictionary/data layout. Why isn't that sufficient for you to write the SAS data step to read the data in the .txt file into a SAS data set?

There are text editors available which can also open .txt of multiple GB. Just Google for them.

I've used UltraEdit (which doesn't come for free) to open the text file.

To get you started I've copied the first 30 lines into the attached sample_2018.txt file.

Re: Reading in 5GB birth dataset from https://www.cdc.gov/nchs/data_access/Vitalstatsonline.htm#Down

Tom — Tue, 18 Feb 2020 01:45:33 GMT

@Flexluthorella wrote:
Right. Going back to my original issue, how do I get to read in ALL the data from the OG (large) file?

First thing is leave the file zipped. No need to unzip it as SAS can unzip it on the file.

Second look at the description of the file and use that to write the code to read it.

So you will have something like this using column oriented reads.

data want;
  infile 'where I put the file.zip' zip truncover member='*' ;
  input var 1-10  var2 $11-12 .... ;
run;

Or perhaps you will want to use formatted mode instead.

data want;
  infile 'where I put the file.zip' zip truncover member='*' ;
  input var 1-10 10.  var2 $2. .... ;
run;

Or some mixture of the two.

Remember look at the data description to understand what data is in which columns. Whether the data is numbers or strings. Some variables that are coded only as digits you might want to read as strings since they are really categorical values and not numbers you could use in operations like MEAN().

Re: Reading in 5GB birth dataset from https://www.cdc.gov/nchs/data_access/Vitalstatsonline.htm#Down

mkeintz — Tue, 18 Feb 2020 02:00:08 GMT

@Flexluthorella wrote:
Right. Going back to my original issue, how do I get to read in ALL the data from the OG (large) file?

Use the layout in the pdf file to set up the necessary INPUT statement to read the data into a SAS data set. You don't need to see the entire raw data set in any editor to do that. And you could first do a test of your program using the 10-record (or some other small) subset of the original raw data.

The full reference to the input statement, including examples, is at Input Statement. There's another possibly useful sas link at Reading Raw Data with the SAS Input Statement

If you haven't done the INPUT statement before, this will be a (worthwhile) experience.

Good luck, and bring back your questions once you start trying to use it.

Re: Reading in 5GB birth dataset from https://www.cdc.gov/nchs/data_access/Vitalstatsonline.htm#Down

SuzanneDorinski — Tue, 18 Feb 2020 05:27:18 GMT

The page where you get the birth data files points users to a page on the National Bureau of Economic Research (NBER) website. If you go to that page on the NBER website and scroll down, you'll see a table for the United States birth data and documentation. Jean Roth at NBER has posted SAS, Stata, and SPSS code to read the ASCII file. She has also posted the file as a Stata file, a SAS data set. and CSV.

The bad news is that she has not done that for the 2018 file. However, I think the record layout for the 2017 file is the same as the record layout for the 2018 file. So, http://data.nber.org/natality/2017/natl2017.sas should help you get started reading the ASCII file into SAS.

I was able to modify the 2017 program to read the zipped version of the 2018 file. One odd note: while the PDF for the 2018 file shows the record length as 1330, the record length is really 1345. The NBER program for 2017 shows 15 variables in columns 1330 to 1345, but those columns are all missing in the 2018 file.

Re: Reading in 5GB birth dataset from https://www.cdc.gov/nchs/data_access/Vitalstatsonline.htm#Down

Flexluthorella — Tue, 18 Feb 2020 19:06:04 GMT

thank you so much!

Re: Reading in 5GB birth dataset from https://www.cdc.gov/nchs/data_access/Vitalstatsonline.htm#Down

Flexluthorella — Tue, 18 Feb 2020 22:34:11 GMT

I am still having issues; I get 0 records read in. Can you share with me?

Re: Reading in 5GB birth dataset from https://www.cdc.gov/nchs/data_access/Vitalstatsonline.htm#Down

SuzanneDorinski — Wed, 19 Feb 2020 02:50:09 GMT

I'll share part of the program in this post, and attach the whole thing. I borrowed some code from the SAS Dummy blog, to figure out the name of the file within the zipped file.

I ran that, and then copied the really long file name into the INFILE statement in the data step.

* borrowing code from https://blogs.sas.com/content/sasdummy/2014/01/29/using-filename-zip/ ;

%let ziploc = /folders/myfolders/NCHS Vital Statistics/Nat2018us.zip;

/* Assign a fileref wth the ZIP method */
filename inzip zip "&ziploc";
 
/* Read the "members" (files) from the ZIP file */
data contents(keep=memname);
 length memname $200;
 fid=dopen("inzip");
 if fid=0 then
  stop;
 memcount=dnum(fid);
 do i=1 to memcount;
  memname=dread(fid,i);
  output;
 end;
 rc=dclose(fid);
run;
 
/* create a report of the ZIP contents */
title "Files in the ZIP file";

proc print data=contents noobs N;
run;

title;

*options obs=100 ;
options obs=max;
*options nocenter ;

**------------------------------------------------ ;
**  by Jean Roth	Thu Oct 12 11:09:27 EDT 2017
**  This program reads the 2017 NCHS Natality Detail Data File  ;
**  Report errors to jroth@nber.org ;
**  This program is distributed under the GNU GPL. ;
**  See end of this file and 
**  http://www.gnu.org/licenses/ for details.      ;
** ----------------------------------------------- ;

*  The following line should contain the directory
   where the SAS file is to be stored  ;

*libname library "/folders/myfolders/NCHS Vital Statistics/";

*  The following line should contain
   the complete path and name of the raw data file.
   On a PC, use backslashes in paths as in C:\  ;

*FILENAME datafile pipe "7z e /homes/data/natality/2017/natl2017.zip  -so ";

*  The following line should contain the name of the SAS dataset ;

%let dataset = natl2018;

DATA &dataset ;

INFILE inzip(Nat2018PublicUS.c20190509.r20190717.txt) zip truncover LRECL = 20000 ;
attrib  dob_yy       length=4     label="Birth Year";        
attrib  dob_mm       length=3     label="Birth Month 01 January";               
attrib  dob_tt       length=4     label="Time of Birth 0000-2359 Time of Birth";

When I tested the code, the options obs=100 statement was uncommented. Once I was sure that the code was correct, I commented out that line, then typed in the options obs=max statement.

Since the data set is 2 GB, I decided that I didn't want to have a permanent version, so I commented out the LIBNAME statement. I commented out the FILENAME statement provided by NBER, because I don't think it will work on my home computer. I'm using the FILENAME statement from the SAS blog instead.

I didn't have to change anything else in the NBER program after the INFILE statement in the data step.