BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Zynep93
Obsidian | Level 7

Hello everyone, 

 

I designed my data in Excel to run in SAS; however, SAS does not read the data. The output data shows some dots. Screenshot is attached. 

I have my different fertilizers treatments (frt) in the first column, the second column is plant height (ph), and the third row is the week that I have, which will be for repeated measures. Since fertilizers are non-numeric values, I put the'' $'' sign next to ''frt''.  Does anyone know what causes this issue and how to correct it? 

  

 

Screenshot 2024-10-09 at 2.59.36 PM.png

 

Screenshot 2024-10-09 at 2.40.27 PM.png

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

If you have inserted actual TAB characters into your in-line data then you can use the EXPANDTABS option of the INFILE statement to have SAS replace them with the proper number of spaces to move to the next tab stop.  Also add the TRUNCOVER option incase any of the lines have fewer values on them than the INPUT statement wants. If you need to represent a missing value in the data use a single period (for either numeric or character variables).

data Waterford;
  infile datalines expandtabs truncover ;
  input frt$ ph Week;
datalines;
XXX 1 2
YYY 2 3
ZZZ . 4
. 5 6
;

Note that most of the SAS editors (and most full functioning text editors) have an option to replace tabs with spaces when typing.  So you can use the TAB key like you would on an actual typewrite, to move to the next tab stop, and the editor will insert the right number of spaces to get there.

View solution in original post

11 REPLIES 11
PeterClemmensen
Tourmaline | Level 20

I suspect you have tabs instead of spaces between your data values?

Zynep93
Obsidian | Level 7
it could be yes, how could I remove that tab?
PeterClemmensen
Tourmaline | Level 20

Try replacing them for spaces or put 

 

dlm=’09’x

in your Infile Statement

Zynep93
Obsidian | Level 7
I put it before the input statement. Did not work.
ballardw
Super User

Did not work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

 

 

The suggestion was an OPTION on an INFILE statement that reads an external file. If you don't have the INFILE point to the correct file, or any file, then it will not work.

Please include the LOG with the whole submitted code as text as shown above. We can't make copy/edit/paste corrections to code that appears in pictures.

Zynep93
Obsidian | Level 7

there is no errors in the log! It did not work out, which means that when I ONLY run the datelines, I still see the dots. I also did put '' infile data lines dlm='09'x;'' after ''data'' statement on the top. I was not sure I put the infile statement in the correct place. This is the log:

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
68
69 data Waterford;
70 infile datalines dlm='09'x;
71 input frt$ ph Week;

 

 

Screenshot 2024-10-09 at 2.40.27 PM.png

 


72 datalines;

NOTE: Invalid data for ph in line 74 1-3.
NOTE: Invalid data for Week in line 74 5-80.




Tom
Super User Tom
Super User

If you have inserted actual TAB characters into your in-line data then you can use the EXPANDTABS option of the INFILE statement to have SAS replace them with the proper number of spaces to move to the next tab stop.  Also add the TRUNCOVER option incase any of the lines have fewer values on them than the INPUT statement wants. If you need to represent a missing value in the data use a single period (for either numeric or character variables).

data Waterford;
  infile datalines expandtabs truncover ;
  input frt$ ph Week;
datalines;
XXX 1 2
YYY 2 3
ZZZ . 4
. 5 6
;

Note that most of the SAS editors (and most full functioning text editors) have an option to replace tabs with spaces when typing.  So you can use the TAB key like you would on an actual typewrite, to move to the next tab stop, and the editor will insert the right number of spaces to get there.

Zynep93
Obsidian | Level 7
Thank you for this. I created the data on Excel; I did not intentionally put or add tabs/spacers. Does Excel automatically add them when I copy from there to paste on SAS? Is there any other way to arrange the data without adding this statement for the next time? I have no problem, but I am just asking.
Tom
Super User Tom
Super User

Excel does insert tabs between cells when you copy and paste the values as text.

 

If you paste that into Display Manager Enhanced editor then the tabs remain.  But when you submit the program from display manager then the tabs in the program editor are converted to spaces automatically. 

 

So I suspect you are using SAS/Studio or some other front end tool for submitting your code where that does not happen.  If you change your preferences in SAS/Studio to replace the tabs then they are NOT pasted into the program editor.

Tom_0-1728568199098.png

 

 

 

 

 

 

Zynep93
Obsidian | Level 7
Yes, I use SAS studio.
Thank you very much again.
Ksharp
Super User
There are must have invisible/unprintable character at end of Week, like TAB CR..
Firstly try to read WEEK in by character type and get rid of these unprintable character in WEEK.

input frt $ ph Week $;
want=input(compress(week,'.', 'kd'),best32.);

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 11 replies
  • 1382 views
  • 4 likes
  • 5 in conversation