topic Re: CSV file import with error in SAS Procedures

CSV file import with error

bbb_NG — Tue, 24 Dec 2013 05:38:12 GMT

Dear All,

I've written 2 patterns of code.(difference is the "*" before "getnames=no")

proc import

datafile="&importpath\_20131224CMCVIPReview\datarequest.csv"

out=_20131224CMCVIPReview

dbms=csv

replace;

*getnames=no;

run;

proc import

datafile="&importpath\_20131224CMCVIPReview\datarequest.csv"

out=_20131224CMCVIPReview

dbms=csv

replace;

getnames=no;

run;

The result for code 1 is all value within the CSV is "< Value not readable>"

The result for code 2 is the correct imports except the row 1 value and the column names are var1,etc.

Can anyone give a hand?

Thanks in advance.

Dawn

Re: CSV file import with error

art297 — Tue, 24 Dec 2013 05:43:54 GMT

Can you post the first couple of lines of the csv file you are trying to import?

Re: CSV file import with error

bbb_NG — Tue, 24 Dec 2013 06:03:33 GMT

Arthur,

Thanks for your attention.

For better understanding I attached the 2 outputs of result for your reference.

The first is the output of code 1,the second is the output of code 2.

Thanks.

Dawn

Re: CSV file import with error

bbb_NG — Tue, 24 Dec 2013 07:39:28 GMT

Arthur,

Attached is some records from CSV file,which is "saved as" from a excel file.

City	Branch	CustName	Hub	Falg	UpgradeDate	UpgradeDate_Format	Hub_Format
上海	GUB	YICK POON MING	5.1	现有 VIP		2013/6/1	CN06131
上海	GUB	WONG CHUI YIN	5.1208	现有 VIP	###	2013/6/1	CNHA07345

Re: CSV file import with error

NaveenSrinivasan — Tue, 24 Dec 2013 11:47:24 GMT

Hi,

I'd like to suggest you to read the csv file in the datastep infile and input. In my experience, I have encountered various issues whilst using Proc import. Presumably, datastep is perhaps more efficient too. Or, there is a possibility of saving your csv file as an xls file and use the import wizard. However, I would assume the dataset is much too large to fit in the xls file.

Datastep option is perhaps better in my opinion.

Thanks,

Naveen

Re: CSV file import with error

art297 — Tue, 24 Dec 2013 14:26:49 GMT

I don't know what additional problems you might confront but, the sample you posted, appears to be tab delimited .. not csv.

Have you tried it with DBMS-tab ?

Re: CSV file import with error

Tom — Tue, 24 Dec 2013 18:47:59 GMT

Sounds like you might be having an encoding issue with the characters that are in the text file.

Re: CSV file import with error

bbb_NG — Wed, 25 Dec 2013 03:11:49 GMT

Dear All,

Thanks for taking a look into my problem.

I will try datasetp with the suggestion of Naveen.

Thanks again for your knid help.

Dawn

Re: CSV file import with error

jakarman — Thu, 26 Dec 2013 11:59:17 GMT

I am seeing the special Eastern language characters once being indicated to be solved by a DBCS approach.

Web/Html and Microsoft office are using utf-8 these days. Most systems however are using latin-1 (western) single byte.

This is what Tom already mentioned as being an encoding problem. You cannot read all chars of utf-8 in a latin-1 only system.

When your SAS system is running latin-1 change it to run utf-8 (different menu lay-out) and import again.

Be aware of the bom (byte order mark) can be added to files editted by a SAS utf-8 system possible making them unusable for programs not aware of utf-8 encoding.

Re: CSV file import with error

GadyKotler — Thu, 26 Dec 2013 17:03:28 GMT

Hi,

If your original data is stored in a XLSX file then you can use the new %xlsx2sas macro to transform it to SAS data set without going through CSV format.

The %xlsx2sas macro supports NLS encoding (UTF-8).

Demo download & free trial at: www.bixforsas.com

Re: CSV file import with error

Trey — Wed, 29 Jan 2014 00:08:03 GMT

See if the following works.

proc import

datafile="&importpath\_20131224CMCVIPReview\datarequest.csv"

out=_20131224CMCVIPReview

dbms=csv

replace;

getnames=yes;

datarow=2;

run;

proc import

datafile="&importpath\_20131224CMCVIPReview\datarequest.csv"

out=_20131224CMCVIPReview

dbms=dlm

replace;

delimiter=",";

getnames=yes;

datarow=2;

run;