DATA Step, Macro, Functions and more

Reading fixed Data problem

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Reading fixed Data problem

Hi, I just started to learn SAS and I was trying to read the following fixed data with'.' as missing value:

 

001 18 53 53 48 73
002 61 81 . 98 1
003 31 65 35 92 35
004 84 42 82 . 19
005 97 33 14 44 9
006 52 40 . 85 93
007 5 . 29 68 37
008 37 42 24 56 32
009 14 54 15 70 39
010 8 32 49 . 17

 

however, when i printed it out, row 7 and 10 are not read properly and I don't know what the problem is.

 

the output:

Screen Shot 2017-10-06 at 4.35.06 PM.png

 

Thank you 


Accepted Solutions
Solution
2 weeks ago
Super User
Super User
Posts: 6,843

Re: Reading fixed Data problem

Hard to tell from photographs but it looks like you might have two issues.

1) You don't seem to have used the right column numbers in your INPUT statement.

2) It also looks like something has replaced some of your spaces with tabs. (NOTE: If you are using SAS as your editor there are options to prevent it from inserting tabs into your programs. I am sure other editors also have those options).

 

It should be easy to fix the first problem, just count better. The second you can fix by using an INFILE statement with the EXPANDTABS option, although that really depends on whether the program that inserted the tabs is using the standard 8 character tabs that SAS will use for expanding the tabs.  (Note there is no need to use DLM=' ' on the INFILE statement since spaces are the default delimiter.

infile datalines truncover expandtabs ;

Note that it also looks like there is no need to read that data as fixed length since there appear to be one or more spaces between every value. missing values are clearly marked with periods and there are no spaces embedded in the middle of the any of the values.

length id $3 ;
input id test1-test5;

View solution in original post


All Replies
Super User
Posts: 19,093

Re: Reading fixed Data problem

[ Edited ]

Post your code and log.

 


leibahay wrote:

Hi, I just started to learn SAS and I was trying to read the following fixed data with'.' as missing value:

 

001 18 53 53 48 73
002 61 81 . 98 1
003 31 65 35 92 35
004 84 42 82 . 19
005 97 33 14 44 9
006 52 40 . 85 93
007 5 . 29 68 37
008 37 42 24 56 32
009 14 54 15 70 39
010 8 32 49 . 17

 

however, when i printed it out, row 7 and 10 are not read properly and I don't know what the problem is.

 

the output:

Screen Shot 2017-10-06 at 4.35.06 PM.png

 

Thank you 


 

New Contributor
Posts: 4

Re: Reading fixed Data problem

Screen Shot 2017-10-06 at 4.34.54 PM.pngScreen Shot 2017-10-06 at 4.57.26 PM.png

Super User
Posts: 19,093

Re: Reading fixed Data problem

post it as text. 


leibahay wrote:

Screen Shot 2017-10-06 at 4.34.54 PM.pngScreen Shot 2017-10-06 at 4.57.26 PM.png


 

New Contributor
Posts: 4

Re: Reading fixed Data problem

Sorry, I am new here. So I don't know the format of the poste Smiley Happy Thank you for your time.

 

data testscore;
infile datalines truncover
dlm=' ';
informat id $3.
test1 2.
test2 2. test3 2. test4 2. test5 2.;
input
id $
test1 5-6
test2 8-9
test3 11-12
test4 13-15
test5 16-18
;
datalines;
001 18 53 53 48 73
002 61 81 . 98 1
003 31 65 35 92 35
004 84 42 82 . 19
005 97 33 14 44 9
006 52 40 . 85 93
007 5 . 29 68 37
008 37 42 24 56 32
009 14 54 15 70 39
010 8 32 49 . 17
;
run;
proc print data=testscore;
format id $3. ;
run;

 

 

Super User
Posts: 19,093

Re: Reading fixed Data problem

You need to use the insert code button otherwise it loses it's spacing, which is important when you're reading a fixed width file. 

 

At any rate, your column indexes are off. Count out your layout again.

 

Respected Advisor
Posts: 4,132

Re: Reading fixed Data problem

@leibahay

In your code you assign INFORMATS to your variables which is great for list input but you then in your input statement you're using column input (defining exactly the from and two column positions where you want to read your data from).

It looks like your data is not exactly in the columns where you expect it to be.

 

Use list input style instead. Below code shows you what you need to change.

data testscore;
  infile datalines truncover
    dlm=' ';
  informat id $3.
    test1 2.
    test2 2. test3 2. test4 2. test5 2.;
  input
    id $
    test1 
    test2 
    test3 
    test4 
    test5 
  ;
  datalines;
001 18 53 53 48 73
002 61 81 . 98 1
003 31 65 35 92 35
004 84 42 82 . 19
005 97 33 14 44 9
006 52 40 . 85 93
007 5 . 29 68 37
008 37 42 24 56 32
009 14 54 15 70 39
010 8 32 49 . 17
;
run;
Solution
2 weeks ago
Super User
Super User
Posts: 6,843

Re: Reading fixed Data problem

Hard to tell from photographs but it looks like you might have two issues.

1) You don't seem to have used the right column numbers in your INPUT statement.

2) It also looks like something has replaced some of your spaces with tabs. (NOTE: If you are using SAS as your editor there are options to prevent it from inserting tabs into your programs. I am sure other editors also have those options).

 

It should be easy to fix the first problem, just count better. The second you can fix by using an INFILE statement with the EXPANDTABS option, although that really depends on whether the program that inserted the tabs is using the standard 8 character tabs that SAS will use for expanding the tabs.  (Note there is no need to use DLM=' ' on the INFILE statement since spaces are the default delimiter.

infile datalines truncover expandtabs ;

Note that it also looks like there is no need to read that data as fixed length since there appear to be one or more spaces between every value. missing values are clearly marked with periods and there are no spaces embedded in the middle of the any of the values.

length id $3 ;
input id test1-test5;
New Contributor
Posts: 4

Re: Reading fixed Data problem

This seems working. What is the function of expandtabs exactly? Haven't seen this yet Smiley Happy

Thank you so much for you time.

 

Super User
Super User
Posts: 6,843

Re: Reading fixed Data problem

It is hard to tell since you posted the data into the text box instead of using the pop-ups for code or SAS code.  That can remove spaces and make other changes.

But it looks to me like your data is NOT in fixed columns like your subject line said. But it does look like you have marked missing value with periods so simple list mode should work.

data want ;
  infile 'myfile' truncover ;
  input id test1-test5;
run;
Frequent Contributor
Posts: 127

Re: Reading fixed Data problem

I have seen many files containing data in fixed width format. None of these had blanks as field separator or dots. So follow Toms suggestion to solve the issue.
☑ This topic is solved.

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

Discussion stats
  • 10 replies
  • 119 views
  • 0 likes
  • 5 in conversation