topic Re: Importing a delimited file into cas in SAS Studio

Importing a delimited file into cas

AmitSri — Mon, 12 Mar 2018 12:35:50 GMT

Hi,

I am trying to import a csv file which is seperated by ";". When I run this query

proc casutil;
   load casdata="20180226_all_markets_export.csv"   incaslib="dbdata"
        importoptions=(filetype="csv" getnames="true")
               casout="20180226_all_markets_export" outcaslib="MYCASLIB" promote;
run;

I am getting the error as:

ERROR: Row data has too many columns.

ERROR: The action stopped due to errors.

Could anyone suggest on resolving this error.

Also is there any option to specify dlm= option in cas? Many Thanks!!

Re: Importing a delimited file into cas

RW9 — Mon, 12 Mar 2018 12:59:09 GMT

Then it is not a CSV file. Comma Separated Variable file = CSV. Yours is a delimited file.

On your issue, what does the CSV look like? How many columns does it have? Does it have correct line ends - i.e. if the file is pulled off Unix then it might not have the correct termstr to read in correctly. Can't really say without seeing the file.

Re: Importing a delimited file into cas

AmitSri — Mon, 12 Mar 2018 13:05:33 GMT

Hi, It was actually a .rpt file. So, I just renamed it with .csv and trying to load it. The file is just seperated with a semicolon. However my script is now running when I placed a diletype argument delimiter=";". Since its a huge file of 120GB, I will post the status after its completion/failure.

Thanks!!

Re: Importing a delimited file into cas

AmitSri — Mon, 12 Mar 2018 13:12:54 GMT

Oops. Again same error. The file has 26 columns and the values are seperated by a ; and the observation ends with \n. Any other arguments to be passed ion the query? Thanks!!

Re: Importing a delimited file into cas

RW9 — Mon, 12 Mar 2018 13:22:59 GMT

You would need to read it manually with a datastep as \n is not a valid termstr. What is likely happening is that SAS cannot find the end of line, and so is reading the whole file as one string. What is an rpt file? Can you not get the original data, its far easier to work with real data than trying to convert output files back to data. Alternatively you could open with notepad or some similar, and find / replace all occurences of "\n" with '0a'x which I think (you will need to check as am in training), en of line character, or '0a'x and '0d'x.

Re: Importing a delimited file into cas

AmitSri — Mon, 12 Mar 2018 14:27:14 GMT

But, Its a huge file of 120Gb. So, unable to do that.Thanks!!

Re: Importing a delimited file into cas

RW9 — Mon, 12 Mar 2018 14:37:44 GMT

Go back to source then and get actual data, don't use output files. I am afraid there isn't an ad-hoc end of line character for \n:

http://support.sas.com/kb/14/178.html

The program which renders that file (e.g. Word) may know that \n is newline, but for files you would only have unix or windows end of line.