BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
leibahay
Calcite | Level 5

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 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

10 REPLIES 10
Reeza
Super User

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 


 

leibahay
Calcite | Level 5

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

Reeza
Super User

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


 

leibahay
Calcite | Level 5

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;

 

 

Reeza
Super User

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.

 

Patrick
Opal | Level 21

@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;
Tom
Super User Tom
Super User

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;
leibahay
Calcite | Level 5

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

Thank you so much for you time.

 

Tom
Super User Tom
Super User

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;
error_prone
Barite | Level 11
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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