DATA Step, Macro, Functions and more

Reading Date - Zeroes Replaced With Spaces

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Reading Date - Zeroes Replaced With Spaces

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;


Accepted Solutions
Solution
‎01-22-2018 07:29 AM
Trusted Advisor
Posts: 1,822

Re: Reading Date - Zeroes Replaced With Spaces

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;

 

View solution in original post


All Replies
Super User
Super User
Posts: 9,193

Re: Reading Date - Zeroes Replaced With Spaces

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.

Solution
‎01-22-2018 07:29 AM
Trusted Advisor
Posts: 1,822

Re: Reading Date - Zeroes Replaced With Spaces

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;

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 70 views
  • 3 likes
  • 3 in conversation