How to read missing data when reading txt file in SAS?

0
votes

I am new to SAS.

I want to read a txt file in SAS. The thing is the file has missing data in some lines.

For example the data is:

James Monroe        Monroe Hall         Virginia          58    4/28/1758   1816
John Quincy Adams   Braintree           Massachusetts     57    7/11/1767   1824    113,142 30.92%
Andrew Jackson      Waxhaws Region      South/North Carolina    61  3/15/1767   1828    642,806 55.93%
Martin Van Buren    Kinderhook          New York    54  12/5/1782   1836    763,291 50.79%
William Henry Harrison  Charles City County     Virginia    68  2/9/1773    1840    1,275,583   52.87%

The columns I want are 'FullName', 'City', 'State', 'Age', DOB', 'Year','Number' and 'Percentage'.

My code is:

infile 'C:\sasfiles\testfile.txt' missover dlm='09'x dsd
input FullName City State Age DOB Year Number PercVote;

Run;

But I get the error

9 CHAR Rutherford B. Hayes.Delaware ..Ohio..54.10/4/1822.1876.4,034,142.47.92% 73 ZONE 5776676676242246767046667676222004666003303323233330333303233323330332332 NUMR 2548526F2402E081953945C1712500099F89F9954910F4F18229187694C034C142947E925 FullName=. City=. State=. Age=. DOB=. Year=54 Number=. PercVote=1876 ERROR=1 N=19 NOTE: Invalid data for FullName in line 20 1-17. NOTE: Invalid data for City in line 20 19-32. NOTE: Invalid data for State in line 20 37-40. NOTE: Invalid data for Number in line 20 46-55. NOTE: Invalid data for PercVote in line 20 62-70.

2
sas
That can't be all your code? Do you have informats for your date vars? Or strings? – Reeza
@Reeza, I don't have informats. – Bigeyes
Try using them. And change MISSOVER to TRUNCOVER. – Reeza
@Reeza. I don't know whether I have to add w.d in the column. For example, input FullName $23.. If I do it in this way, it may bring some of substring of the next column to it. And for the original input text file, should I pre-process it? Otherwise, how can I align the column? – Bigeyes

2 Answers

1
votes

You need to provide data in a format that can by properly parsed.

When using delimited data then use adjacent delimiters to indicate there is a value missing. It is best to define the variables before you use them. If you define them in order then your input statement can be very simple.

data want ;
   infile cards dsd dlm='|' firstobs=2 truncover ;
   length name $13 city $11 state $15 age 8 dob 8 yod 8 ;
   input name -- yod ;
   informat dob mmddyy10.;
   format dob yymmdd10.;
cards;
----+----0----+----0----+----0----+----0----+----0----+----0----+----0
James Monroe|Monroe Hall|Virginia||4/28/1758|1816
John Quincy Adams|Braintree|Massachusetts|57|7/11/1767|1824 
;

Or force the data into columns and use column input.

data want ;
   infile cards firstobs=2 truncover ;
   input name $ 1-13 city $ 19-29 state $ 31-45 age 47-48 @50 dob mmddyy10. yod 61-64;
   format dob yymmdd10.;
cards;
----+----0----+----0----+----0----+----0----+----0----+----0----+----0
James Monroe      Monroe Hall Virginia            4/28/1758 1816
John Quincy Adams Braintree   Massachusetts   57  7/11/1767 1824 
;
0
votes

Try this:

data want;
infile cards dlm='09'x missover;
input (FullName City State) (:$32.) Age :8. DOB :mmddyy9. Year :4. Number :comma8. PercVote :percent8.;
format DOB mmddyy10. number comma16.  percvote percent6.2;
cards;
James Monroe        Monroe Hall         Virginia          58    4/28/1758   1816
John Quincy Adams   Braintree           Massachusetts     57    7/11/1767   1824    113,142 30.92%
Andrew Jackson      Waxhaws Region      South/North Carolina    61  3/15/1767   1828    642,806 55.93%
Martin Van Buren    Kinderhook          New York    54  12/5/1782   1836    763,291 50.79%
William Henry Harrison  Charles City County     Virginia    68  2/9/1773    1840    1,275,583   52.87%
;
run;