topic Re: Reading fixed Data problem in SAS Programming

Reading fixed Data problem

leibahay — Fri, 06 Oct 2017 20:38:39 GMT

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:

Thank you

Re: Reading fixed Data problem

Reeza — Fri, 06 Oct 2017 20:55:44 GMT

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:

Thank you

Re: Reading fixed Data problem

leibahay — Fri, 06 Oct 2017 20:58:28 GMT

Re: Reading fixed Data problem

Reeza — Fri, 06 Oct 2017 21:22:00 GMT

post it as text.

@leibahay wrote:

Re: Reading fixed Data problem

leibahay — Fri, 06 Oct 2017 21:25:38 GMT

Sorry, I am new here. So I don't know the format of the poste 🙂 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;

Re: Reading fixed Data problem

Reeza — Fri, 06 Oct 2017 21:38:16 GMT

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.

Re: Reading fixed Data problem

Tom — Fri, 06 Oct 2017 22:03:34 GMT

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;

Re: Reading fixed Data problem

Tom — Sat, 07 Oct 2017 03:44:20 GMT

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;

Re: Reading fixed Data problem

Patrick — Sat, 07 Oct 2017 13:24:42 GMT

@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;

Re: Reading fixed Data problem

error_prone — Sat, 07 Oct 2017 13:37:25 GMT

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.

Re: Reading fixed Data problem

leibahay — Sat, 07 Oct 2017 23:55:15 GMT

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

Thank you so much for you time.