topic Re: Reading Date - Zeroes Replaced With Spaces in SAS Programming

Reading Date - Zeroes Replaced With Spaces

cgray — Tue, 16 Jan 2018 14:33:45 GMT

Hopefully an easy one for the experts this morning:

I have a program with column inputs to read a fixed width .dat file of birth certificate data. In years past, dates were listed with zeroes (e.g., 20150101). The newest file replaces the zeroes with spaces (e.g., 2015 1 1), and I am getting several missing values for any variables with dates. I figure this has something to do with SAS interpreting the space as a delimiter within these fields? I thought it would read all of the data between column 132 and column 140 regardless because of using 8. in yymmdd8.

Thanks in advance.

data test;
infile '\\FILEPATH\birth2016.dat' truncover lrecl=4096;
input
@1 transcode $1.
@2 century $2.
@4 birthyr $2.
@6 certno $5.
@63 fname $40.
@103 lname $25.
@128 suffix $4.
@132 DOB yymmdd8.

@140 hourbirth $2.

[REMAINING CODE for 50+ variables]

run;

Re: Reading Date - Zeroes Replaced With Spaces

RW9 — Tue, 16 Jan 2018 14:50:31 GMT

Get the file fixed. "2015 1 1" is pretty much meaningless in terms of any format. There is no standard like that I can think of. You could read in as text, then tranwrd in the 0's, but I would push the work back on the person who cannot supply you good data.

Re: Reading Date - Zeroes Replaced With Spaces

Shmuel — Tue, 16 Jan 2018 14:50:40 GMT

Assuming the date field is DOB -

1) read it as char type DOB_char $8.

2) replace spaces by zeros: DOB = translate(DOB_char,'0',' ');

3) use input function to get the sas numeric date: DOB = input(DOB_char, yymmdd8.);

4) optionally drop the origin date: DROP DOB_char;