Proc import issue in Unix SAS

[deleted_user]

Hi,

I met with a problem when using proc import in Unix SAS, the last variable can not be read correct, here is the code:

proc import out=t1
datafile="/home/test.csv"
dbms=csv replace;
getnames=yes;
datarow=2;
run;

proc contents data=t1;
run;

The dataset test.csv is
id state sex age
1 VA M 26
2 DC F 27
3 CA F 23
4 FL M 29
5 IL F 31

The SAS log reads as:

1 proc import out=t1
2 datafile="/home/test.csv"
3 dbms=csv replace;
4 getnames=yes;
5 datarow=2;
6 run;

Name age
is not a valid SAS name.
Problems were detected with provided names. See LOG.
7 /**********************************************************************
8 * PRODUCT: SAS
9 * VERSION: 9.1
10 * CREATOR: External File Interface
11 * DATE: 28APR09
12 * DESC: Generated SAS Datastep Code
13 * TEMPLATE SOURCE: (None Specified.)
14 ***********************************************************************/
15 data T1 ;
16 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
17 infile '/home/test.csv' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
18 informat id best32. ;
19 informat state $2. ;
20 informat sex $1. ;
21 informat VAR4 $3. ;
22 format id best12. ;
23 format state $2. ;
24 format sex $1. ;
25 format VAR4 $3. ;
26 input
27 id
28 state $
29 sex $
30 VAR4 $
31 ;
32 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
33 run;

and proc contents list as:

Alphabetic List of Variables and Attributes

# Variable Type Len Format Informat

4 VAR4 Char 3 $3. $3.
1 id Num 8 BEST12. BEST32.
3 sex Char 1 $1. $1.
2 state Char 2 $2. $2.

You can see the last variable age is not read correctly: first it failed to import the variable name age but replaced with VAR4, second it is read as character in stead of numeric.

Last time I used infile to read the data there was an issue with the last variable too, data_null_; kindly advised adding termstr=crlf to resolve the problem. I doubt this issue is an integration issue(Unix SAS with PC) too, can anybody suggest how to get the issue resolved?

Thanks so much for attention and help!

[Doc_Duke]

I suspect the .csv file is coming from a PC. If so, this is a common problem. The end-of-line marker for a text file is different on a PC than it is on Unix.

Run the doc2unix utility on Unix to get the right end of line and then you can get the data in reasonably.

[deleted_user]

Yes the csv file is from PC, can you advise in more detail how to run the doc2unix utility?

[deleted_user]

Hello Beickham,

Doc made a little spelling mistake. It is dos2unix.

"DOS text files traditionally have CR/LF (carriage return/line feed) pairs as their new line delimiters while Unix text files traditionally have LFs (line feeds) to terminate each line" (cited from Ubuntu/tofromdos documentation)

dos2unix will convert CR/LF to LFs.

If you are running sas under debian/linux, the installation of the tofromdos package will provide you with the dos2unix and unix2dos utilities.

Regards,

yoba

[Doc_Duke]

Thanks for the catch. Our Unix Admin had already installed it the first time I needed it.

I think the syntax is
dos2unix pcfilename > unixfilename
It follows the usual Unix command conventions. If it is already installed, it is also likely that the documentation is in your man pages.

If it is easier, you can download a PC version of the utility and do the conversion before moving it to the Unix box.