BookmarkSubscribeRSS Feed
vmuoio
Calcite | Level 5

Hello all,

I am writing because I am having an issue inputing data into SAS. I have a spreadsheet of 136 rows and 19 columns. SAS is only recognizing 68 records and a varying number of observations and I am really losing my mind because I have never encountered this before, and when I tested each column to see if one of them was the culprit (adding each column's data via datalines) they were all read as 136!. It is only when I try to import the whole spreadsheet either via datalines or infile 'c:\.....' that it doesn't work.

Things I have tried:

- infile 'c:\mydata.prn';

-datalines;

-putting a blank row in between each column of my excel spreadsheet before converting it to prn for use with SAS.

-making the columns in my excel sheet very wide and centering the numbers before converting to prn

If anyone has any ideas about why this might be happening or solutions (perhaps uploading each variable separately and then combining, if this is a possibility) please please let me know!!

Best wishes!

6 REPLIES 6
vmuoio
Calcite | Level 5

Also I forgot to add that in the log, it is telling me that the minimum and maximum record length was 256 (not sure what this means as I only have 136 rows in my spreadsheet and 19 columns) and that one or more lines were truncated.

Patrick
Opal | Level 21

Easiest would be if you could attach your file. It has an extension of .prn but the log message you refer to actually implies it's a simple text file.

If not writing explicitly then SAS uses a lot of defaults. This helps you normally to get to results with less coding but for a newby it can be tricky because something might not work and you don't know why. It's most of the time about changing the default by coding for it explicitly.

"record length was 256" is the maximum number of characters SAS will read in a line. If your row is longer then it will get truncated. LRECL allows you to define a longer record length.

What you might try:

....

infile 'c:\mydata.prn' lrcl=1000 truncover;

input ....

Tom
Super User Tom
Super User

You probably just need to add TRUNCOVER and LRECL options to your INFILE statement.

LRECL should be set to something much larger than 256.

TRUNCOVER will tell to NOT go to a new line when there are too few values on the current line for the number of variables you are trying to read.

Save the file as CSV from Excel.

Try just reading all of the columns as text and see what you get.

data want ;

  infile 'myfile.csv'  dsd lrecl=30000 truncover termstr=crlf ;

  length col1-col19 $200 ;

  input col1-col19 ;

run;

vmuoio
Calcite | Level 5

Dear Tom,

THANK YOU SO MUCH!!!!!!! I have spent at least 20 hours trying to do this. I so so so appreciate your help!!!!!!!! IT now says that 136 records were read that the data set had 136 observations and 19 variables. Does this mean that everything is there?

I wish you a very very very merry Christmas and happy new year!!!

Tom
Super User Tom
Super User

With such as small file just use PROC PRINT to look at what it read.

Reeza
Super User

Please mark the question answered as well Smiley Happy

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!

What is ANOVA?

ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 2773 views
  • 3 likes
  • 4 in conversation