topic Re: SAS only reading every other record- Please help me! in Statistical Procedures

SAS only reading every other record- Please help me!

vmuoio — Tue, 23 Dec 2014 00:42:04 GMT

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!

Re: SAS only reading every other record- Please help me!

vmuoio — Tue, 23 Dec 2014 00:43:30 GMT

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.

Re: SAS only reading every other record- Please help me!

Tom — Tue, 23 Dec 2014 01:03:14 GMT

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;

Re: SAS only reading every other record- Please help me!

Patrick — Tue, 23 Dec 2014 01:05:25 GMT

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 ....

Re: SAS only reading every other record- Please help me!

vmuoio — Tue, 23 Dec 2014 01:29:42 GMT

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!!!

Re: SAS only reading every other record- Please help me!

Tom — Tue, 23 Dec 2014 01:43:48 GMT

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

Re: SAS only reading every other record- Please help me!

Reeza — Tue, 23 Dec 2014 02:19:11 GMT

Please mark the question answered as well