topic Re: Reading csv data, var length in SAS Programming

Reading csv data, var length

evp000 — Fri, 04 Dec 2015 14:58:57 GMT

Hi all,

Say I have this data in a csv file (C:\Users\xxxx\Desktop\test.csv):

/*
co11, longstring, col3
a,one two three four,b
c,five six seven eight nine,d
*/

When I read it with an input statement, I have to specify a length (or informat) but I want it to take the length of the longest value.

If I use proc import the length is longer than the longest value. Why?

proc import datafile = 'C:\Users\xxxx\Desktop\test.csv'
out = test3
dbms = csv
replace;
getnames = yes;
run;

proc print; run;
proc contents; run; /* length = 15 */

proc sql;
select max(length(longstring)) /* = 14 */
from test3;
quit;

How would I read it so that the length is 14? I've tested this on other data too, where the longest value is 24 and the length created is 26.

Thanks.

Re: Reading csv data, var length

FreelanceReinh — Fri, 04 Dec 2015 15:20:19 GMT

Hi, this is an interesting question. However, I was not able to replicate your result. With your sample .csv data I obtain length 25 from both PROC CONTENTS and PROC SQL -- in perfect agreement with the fact that "five six seven eight nine" is a string consisting of 25 characters. (Actually, the names of the second and third variable were prefixed by an underscore due to the leading blanks in the first line of your data.)

Re: Reading csv data, var length

RW9 — Fri, 04 Dec 2015 15:25:10 GMT

Why do you need to make it length of the longest variable out of interest, it really doesn't matter that much.

As for length of the variable this would of course be defined up front in you Data Transfer Specification agreement which will detail the strcuture of the data being sent, you do have one yes? Take the length from there.

If not, then you will need to read the file, and then post process it with some logic, howevedo suggest you look at transfer agreements as a better, more robust solution:

proc sql;
  create table LENGTHS as
  select "NAME" as NAME,max(length(NAME)) as M from SASHELP.CLASS;
quit;

data _null_;
  set lengths;
  call execute('data want; length '||strip(name)||' $'||strip(put(m,best.))||'.; set sashelp.class; run;');
run;

Re: Reading csv data, var length

evp000 — Fri, 04 Dec 2015 15:27:21 GMT

Thanks for testing it. I had shortened the text when I ran the test, but I put it back to the example and now I get:

longstring Char 29 $29. $29.

longest value:
25

I checked for leading or trailing blanks (in notepad) and don't see any.

Re: Reading csv data, var length

evp000 — Fri, 04 Dec 2015 15:30:50 GMT

It's really just a QC issue. We are to keep the "original" length, but of course that length is going to be different depending on how the data was read. In this case the production version and the QC version don't match.

Yes, thanks, I know I can do post-processing to change it.

For our data transfer specs, length is not specified if the data comes in a csv file.

Thanks for your INPUT.

Re: Reading csv data, var length

FreelanceReinh — Fri, 04 Dec 2015 15:39:03 GMT

Can you perhaps attach the small sample .csv file, so that we can check if the same discrepancies occur using our SAS installations?

Re: Reading csv data, var length

evp000 — Fri, 04 Dec 2015 15:44:18 GMT

Here it is. change the 'txt' back to 'csv'.

Thanks again for checking.

Re: Reading csv data, var length

FreelanceReinh — Fri, 04 Dec 2015 16:02:31 GMT

Thanks for providing the file. Using your PROC IMPORT code I still get length 25 in both cases:

longstring Char 25 $25. $25.

I have also opened the file with a hex editor and haven't found anything suspicious.

Finally, I retyped the file with Notepad and compared it to your file with a comparison tool and found no difference.

So, it's still a mystery to me what happened on your side.

Re: Reading csv data, var length

FreelanceReinh — Fri, 04 Dec 2015 16:13:01 GMT

Just an idea: Could it be a DBCS issue?

What do you get from %put %sysfunc(getoption(dbcs)); ?

Re: Reading csv data, var length

evp000 — Fri, 04 Dec 2015 16:13:17 GMT

Ok, thanks for trying it out.

Re: Reading csv data, var length

evp000 — Fri, 04 Dec 2015 16:15:51 GMT

NODBCS

Re: Reading csv data, var length

FreelanceReinh — Fri, 04 Dec 2015 16:28:28 GMT

Ok, thanks.

When you provided the sample file you wrote "change the 'txt' back to 'csv'". This sounds to me as if the file you attached was not the original .csv file from your PROC IMPORT experiment. And this, in turn, would obviously open the possibility that some strange characterisic of the .csv file got lost in the process of "converting" it to a .txt file. How did you create the .txt from the .csv file?

Re: Reading csv data, var length

evp000 — Fri, 04 Dec 2015 16:33:41 GMT

I just copied the file and renamed it with the txt extension, since you can't attach csv files.

Re: Reading csv data, var length

Reeza — Fri, 04 Dec 2015 16:34:17 GMT

If this is a file that is going to be read across a testing and prod environment it seems worth it to me to write a data step to import the data, rather than rely on Proc Import.

The data step will allow you to specify the lengths as desired.

I think the expectation that PROC IMPORT will produce the same results across different environments is a big assumption and can't be trusted.

Re: Reading csv data, var length

evp000 — Fri, 04 Dec 2015 16:37:05 GMT

Yes, specifying the length is what I'm pushing for. And it would be consistent across projects.

Re: Reading csv data, var length

FreelanceReinh — Fri, 04 Dec 2015 17:33:37 GMT

Indeed, for a quick look into a .csv file with SAS, the IMPORT procedure is convenient. It can also generate a code template for an import data step. But when used for production purposes, those convenient automatisms constitute a risk. From a QC point of view, a procedure statement called "GUESSINGROWS" should make the alarm bells ring.

Still, I am curious about the reason of those length discrepancies. If I were to track that down, I would reduce/increase the actual length of the affected string, in order to find out exactly where the discrepancies start. Then I would make other small modifications to the file, and so on.

Re: Reading csv data, var length

ballardw — Fri, 04 Dec 2015 18:41:00 GMT

Proc Import should have created a datastep program visible in the Log. Copy that to the editor and modify to you hearts content, especially the informats.

Of course if your example file doesn't contain something the length of the longest value that will ever appear (specification document!!) then there can be issues with reusing the code.