Help using Base SAS procedures

CSV file import with error

Reply
Frequent Contributor
Posts: 99

CSV file import with error


Dear All,

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

1.

proc import

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

out=_20131224CMCVIPReview

dbms=csv

replace;

*getnames=no;

run;

2.

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

PROC Star
Posts: 7,489

Re: CSV file import with error

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

Frequent Contributor
Posts: 99

Re: CSV file import with error

ResultOfCode1.pngResultOfCode2.pngArthur,

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

Frequent Contributor
Posts: 99

Re: CSV file import with error

Arthur,

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

CityBranchCustNameHubFalgUpgradeDateUpgradeDate_FormatHub_Format
上海GUBYICK POON MING 5.1现有 VIP2013/6/1CN06131
上海GUBWONG CHUI YIN5.1208现有 VIP###2013/6/1CNHA07345
Occasional Contributor
Posts: 18

Re: CSV file import with error

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

PROC Star
Posts: 7,489

Re: CSV file import with error

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   ?

Super User
Super User
Posts: 7,074

Re: CSV file import with error

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

Frequent Contributor
Posts: 99

Re: CSV file import with error

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


Trusted Advisor
Posts: 3,215

Re: CSV file import with error

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.

---->-- ja karman --<-----
New Contributor
Posts: 3

Re: CSV file import with error

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

Occasional Contributor
Posts: 5

Re: CSV file import with error

See if the following works.

proc import

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

out=_20131224CMCVIPReview

dbms=csv

replace;

getnames=yes;

datarow=2;

run;

OR

proc import

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

out=_20131224CMCVIPReview

dbms=dlm

replace;

delimiter=",";

getnames=yes;

datarow=2;

run;

Ask a Question
Discussion stats
  • 10 replies
  • 588 views
  • 1 like
  • 7 in conversation